"mysql_fetch_assoc()" error when data in mysql field is changed

Question

I have a mySQL database from where I fetch some data via PHP.

This is what I've got:

if ($db_found) {

    $URL_ID = $_GET["a"];

    $SQL = "SELECT * FROM tb_employees WHERE URL_ID = $URL_ID";
    $result = mysql_query($SQL);

    while ($db_field = mysql_fetch_assoc($result)) {
        $firstname = $db_field['firstname'];
        $surname = $db_field['surname'];
        $function = $db_field['function'];
        $email = $db_field['email'];
        $telnr = $db_field['telnr'];
    }

    mysql_close($db_handle);
}
else {
    print "Database not found... please try again later.";
    mysql_close($db_handle);
}

The URL_ID field in my mySQL database is, for this example, 001. When I go to www.mydomain.com/index.php?a=001 it fetches all the data, puts it into a variable, and I can echo the variables without any problem.

Now, I want to change the URL_ID, and I've changed it to "62ac1175" in the mySQL database. However, when I proceed to www.mydomain.com/index.php?a=62ac1175, I get this error message:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in mydomain.com\db_connect.php on line 17

The field in mySQL has varchar(8) as type and utf8_general_ci as collation.

If I change the entry back to 001 and change my URL to ?a=001, it works fine again.

What's going wrong?

Answer 1

You are not doing any error checking in your query, so it's no wonder it breaks if the query fails. How to add proper error checking is outlined in the manual on mysql_query() or in this reference question.

Example:

$result = mysql_query($SQL);

if (!$result)
 { trigger_error("mySQL error: ".mysql_error());
   die(); }

your query is breaking because you aren't wrapping the input in quotes. You can avoid* quotes only for integers (which 62ac1175 is not). Try

$SQL = "SELECT * FROM tb_employees WHERE URL_ID = '$URL_ID'";

Also, the code you show is vulnerable to SQL injection. Use the proper sanitation method of your library (like mysql_real_escape_string() for the classic mysql library that you are using), or switch to PDO and prepared statements.

In your code, this would look like so: Instead of

$URL_ID = $_GET["a"];

do

$URL_ID = mysql_real_escape_string($_GET["a"]);

_{* however, if you avoid quotes, mysql_real_escape_string() won't work and you need to check manually whether the parameter actually is an integer.}