Script to match names submitted via a form with MySQL database breaks when name contains apostrophe

Question

I have script that enters names into a MySQL database, using mysql_real_escape_string so that apostrophes are handled correctly. The trouble I am experiencing is with the script below that checks to see if names entered using another form correspond to names already in my database and if names are found updates the row.

When I try to enter a name with an apostrophe into the form processed by this script, I get an error message stating that the name wasn't found, and the name in the error message contains a backslash before the apostrophe, which is obviously the issue.

So the question is, how can I amend the script below so that it will work with names with apostrophes?

Thanks,

Nick

$row_count = count($_POST['name']);
if ($row_count > 0) {

    mysql_select_db($database, $connection);
    $name = array();
    $workshop = array(); 
    $not_found = array();

    for($i = 0; $i < $row_count; $i++) {
        // variable sanitation...
        $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
        $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
    }
    $names = "('".implode("','",$name)."')";

    $not_in = Array();

    // lets say all names doesn't exist in `conference`
    foreach($name as $value) {
        // names in array are keys, not values
        $not_in[$value] = true;
    }


    $query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
    while(list($dbname) = @mysql_fetch_row($query)) {
        // delete those name from $not_in who exists
        unset($not_in[$dbname]);
    }

    // names in $not_in array are keys, not values
    $not_in = array_keys($not_in);

    if(empty($not_in)) {
        // its ok, all names have been found. do the magic.
        for($i = 0; $i < $row_count; $i++) {
            $sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '$name[$i]'";
            mysql_query($sql);
            $body .= "Name: " . $name[$i] . "    Workshop: " . $workshop[$i] . "\n\n";
        }

Answer 1

Hmmm! I think I might have found the issue. The problem might not be with the query but with the PHP code. I'll try to explain below using your example John O'Shea.

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    $name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('".implode("','",$name)."')";

$not_in = Array();

// lets say all names doesn't exist in `conference`
foreach($name as $value) {
    // names in array are keys, not values
    $not_in[$value] = true;
}

After the above code, Array $not_in will contain escaped keys because $name already contains values escaped using mysql_real_escape_string(). Hence, for example:

$not_in[John] = true; $not_in[John O\'Shea] = true;

$query = mysql_query("SELECT Name FROM conference WHERE Name IN $names"); 
while(list($dbname) = @mysql_fetch_row($query)) {
    // delete those name from $not_in who exists
    unset($not_in[$dbname]);
}

Now $dbname in the above code contains unescaped values retrieved from the DB, for example John O'Shea without the backslashes. Since this is not what $not_in contains, the unset() will not work. This means that all apostrophe values remain in the $not_in array.

So the fix is to keep unescaped values in $not_in.

Hope this makes sense!

========== EDIT: In response to how to keep unescaped values in $not_in:

The idea is to do escaping just where it is needed. Here are the changes that you may do to your code:

Rewrite the first for() as below:

for($i = 0; $i < $row_count; $i++) {
    // variable sanitation...
    //$name[$i] = mysql_real_escape_string(ucwords($_POST['name'][$i]));
    $name[$i] = ucwords($_POST['name'][$i]);
    $workshop[$i] = mysql_real_escape_string($_POST['workshop'][$i]);
}
$names = "('" . mysql_real_escape_string(implode("','",$name)) . "')";

And rewrite the UPDATE statement as:

$sql = "UPDATE conference SET Workshop = '$workshop[$i]' WHERE Name LIKE '" . mysql_real_escape_string($name[$i]) . "'";

By the way, According to your code, the UPDATE will not run if there is one name that does not exist in the database. Is it absolutely necessary to run the UPDATE only if all the $_POST['name'] are found in the database? If not, you can significantly reduce the amount of code.

I haven't tested the above changes but I think they should work. Let me know if you get any issues.

========== EDIT 2: Code snippet for updating records that exist and generating errors for records that did not

Hey Nick, I think only writing the below code should do the trick:

$row_count = count($_POST['name']);
if ($row_count > 0) {
    mysql_select_db($database, $connection);
    for ($i = 0; $i < $row_count; $i++) {
        mysql_query("UPDATE conference SET Workshop = '" . mysql_real_escape_string($_POST['workshop'][$i]) . "' WHERE Name LIKE '" . mysql_real_escape_string($_POST['name'][$i]) . "'");
        $affectedRows = mysql_affected_rows();
        if ($affectedRows == 0) {
            echo '<br>Name did not exist - ' . $_POST['name'][$i];
        }
    }
}

Hope this helps!