4

[UPDATED] with new code "sql_real_escape_string()"
[UPDATED] if anyone wants to look at the site its at Test site
[UPDATED] with the while code showing any results via echo

Hello All,

I have looked at many posts on this matter, but simply cannot understand why the following code doesn't work:

    $username = $_POST['username'];

    // get the record of the user, by looking up username in the database.  
    $query = sprintf("SELECT UserName, Password FROM userlogin WHERE UserName='%s'", mysql_real_escape_string($username));

    $result = mysqli_query($dbc, $query) or 
        die ("Error Querying Database for: " . $query . 
        "<br />Error Details: " . mysql_error() . "<br/>" . $result);

while ($row = mysqli_fetch_assoc($result))
{
         Echo($row['UserName']);
}

The Code seems to be correct... the database is working perfectly (for input purposes) and the connection is a shared connection applied with require_once('databaseconnection.php'); that is working for the registration side of things.

like normal I'm sure this is something simple that I have overlooked but cannot for the life of me see it!

I do not get any error messages from the myssql_error() its simply blank.

any help would be much appreciated.

Regards

Improve this question
9
  • What do you get when you echo $query? Do you get a valid sql statement? Commented May 29, 2011 at 14:01
  • also, it might help to show a few more lines of code where you are trying to echo what you find in the database. You can also change the query to query = "SELECT UserName, Password FROM userlogin WHERE UserName=$username"; and it might be a good idea to sanitise the username post variable by using $username = mysql_real_escape_string($_POST['username']); Commented May 29, 2011 at 14:06
  • The code i get from the from echo $query is " SELECT UserName, Password FROM userlogin WHERE UserName=PCH " PCH being the variable username i entered. I have since changed the $query to code $query = sprintf("SELECT UserName, Password FROM userlogin WHERE UserName='%s'", mysql_real_escape_string($username)); but still no luck Commented May 29, 2011 at 14:12
  • Can you post how you are reading the results from $data? Commented May 29, 2011 at 14:21
  • At the moment I was going to print it screen using code while($item == mysqli_fetch_array($data)) { echo($item); } 'code' But the code does not get that far, it "die"s when querying before getting to the while loop. Commented May 29, 2011 at 14:24

6 Answers 6

Reset to default
2

Check the username you try to query as it might be empty. Do you really use a post-request to run that script? How do you verify that it does not work? What do you do with $data after the query?

If just nothing seems to happen it is likely your query did not match any record. Check for whitespace and case of the username you are looking for.

Mind those warnings:

  • Use a prepared statement or at least sql-escape any user-input before using it in sql.
  • Don't use die in serious code only for debugging.
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I had just updated my query to "$query = sprintf("SELECT UserName, Password FROM userlogin WHERE UserName='%s'", mysql_real_escape_string($username));" just before you posted. The $data was just a troubleshooting thing, I actually intend on making the script check for both username and passwords and if matching do A else B.
Have you checked the content of $username? Have you checked whitespaces and case for the matching string in the db? Oh and update your question with your current code please.
Updated. and Yes I have the query print out on each fail, I have copy pasted this query directly into phpmyadmin and it works as expected. I know the database is connecting properly (a: because it doesnt die on conection and b: because I use the same share connection script in another page that adds users and works well)
Have you tried using a select without a where-clause? If that does not work, it is a deeper problem. Are you sure you are using mysqli? Have you tried closing the $dbc before using it? That should produce an error. If no error happens, then the connention-resource is bogus.
Thing is I have another page that uses the same databaseonnection from a sharescript and mysqli_query and it works fine inserting new records. I have just tried the Select statement without a where clause, and im getting the same response. I also just tied to close the database prior to connection and it throws an error which is expected.
|
2

The $data will contain a result object. You need to iterate over it using something like mysqli_fetch_assoc($data).

Also, you can interpolate variables directly into double quoted strings - i.e. UserName='".$username."'" could be written more cleanly as UserName='$username' rather than breaking out of the string.

Also, please sanitize your input - all input is evil - using mysqli_real_escape_string() function. You've got a SQL injection exploit waiting to happen here.

Bear in mind that it's a very good idea to validate all data to be inserted into a database.

Improve this answer

3 Comments

Thanks I have changed the query to use mysql_real_escape_string(). I have a while() loop to go through any data that is returned, should only 1 record, but just incase.
Something like while ($row = mysqli_fetch_assoc($result)) { } will do the trick.
If it's only one record, include LIMIT 1 at the end of the SQL statement. It'll stop the DB engine after it finds the first result. You could in this case change the above code to an if statement rather than a while loop - makes a little bit more sense.
1

Very often you have problems with query itself, not implementation. Try it in phpMyAdmin first and see if there are any problems. Check server logs.

BY THE WAY: Never put variables from POST to query! That's definitely a SQL injection'

Improve this answer

1 Comment

Thanks I have changed the query to use mysql_real_escape_string(). Also PHPmyadmin runs the copy pasted query output, perfectly as expected...
1

You might have some issue with the query. Have you Tried to echo the $query and run that directly with mysql client or workbench?

Improve this answer

1 Comment

Yes I have tried the query, and it seems to be fine when copy paste direct into phpmyadmin.
1

This piece of code seems ok. That is, if $dbc contains an actual database connection. But the choice of naming that variable $data while the function actually returns a result object or a boolean, indicates that you may process the data wrong.

If that is not the problem, we'll definately have to see more code.

Improve this answer

2 Comments

yeah the $data thing was just through tweaking i copy-pasted code that I knew worked to try and trouble shoot. no dice.
also I know $dbc is working as its a shared script using Require_once(); and is working perfectly in other scripts.
1

Try printing $data variable instead of printing only query. Check, whether you are able to get any error messages. If you could see any data then you should use mysql fetch function to iterate things. Try it.

Improve this answer

1 Comment

I actually just tried that and its empty, I suppose I would expect it to be since the query is "die"ing...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.