Codemonkey Codemonkey - 2 months ago 22
MySQL Question

How to escape a string to insert into MySQL via PDO *without* using prepared statements (legacy app)

I know that prepared statements are the way to go, but I have a large legacy application that I'm converting across.

I've changed it from mysqli to PDO as the first step, and will now be converting all the queries in it to prepared statements. But there's hundreds of them, so that will take time.

In the interim, what function should I be using to escape strings?

I tried

$PDO->quote
, but it appears to do 2 things:

1) surround a string with quotes
2) convert ' in the string to \'

I can trim the start/finish quote off easily enough, but I then end up with \' being inserted into the database. I think the correct escaping would be to convert ' to '', so I'm not sure why it's doing a backslash instead?

I KNOW this is horrific, but this is the kludge I've come up with in the meantime...

$s = str_replace("''","'",$s);
$s = str_replace("''","'",$s);
$s = str_replace("''","'",$s);
$s = str_replace("''","'",$s);
$s = str_replace("''","'",$s);
$s = str_replace("''","'",$s);

$s = str_replace("'","''",$s);


What should I be doing instead? And again, this is as a stopgap while I convert everything over to prepared statements.




This is how the app used to do it, mysqli-style:

function SQLSafe($s) {
global $DB;
$s = get_magic_quotes_gpc() ? stripslashes($s) : $s;
$s = $DB->escape_string($s);
return $s;
}


where $DB is

$DB = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
$DB->set_charset('utf8');
$DB->query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'");


and used like:

$DB->query("INSERT INTO USERS (username) VALUES ('" . SQLSafe($username) . "')");

Answer

As far as I know, mysqli_real_escape_string() and PDO::quote() behave similarly in MySQL context—the only difference is that the former does not add surrounding quotes, so you use it this way:

$email = mysqli_real_escape_string($conn, filter_input(INPUT_POST, 'email'));
$sql = "SELECT user_id, user_name
    FROM user
    WHERE email='$email'";

... and the latter does add them so you use it this way:

$email = $pdo->quote(filter_input(INPUT_POST, 'email'));
$sql = "SELECT user_id, user_name
    FROM user
    WHERE email=$email";

If there're too many queries to get SQL fixed, I suggest you write a custom wrapper function and search+replace mysqli_real_escape_string calls with it, e.g.:

/**
 * Emulates mysqli_real_escape_string()
 * 
 * @param PDO $pdo
 * @param mixed $input
 * @return string Escaped input, not surrounded in quotes 
 */
function pdo_real_escape_string (PDO $pdo, $input) {
    $output = $pdo->quote($input);
    return $output[0] === "'"
        ? mb_substr($output, 1, -1, 'utf-8' /* or whatever */)
        : $output;
}

Beware that this is an ugly hack and prepared statements are the only sensible method. For instance, null values will become empty strings (''). It's only meant as temporary replacement for a similarly ugly hack.

It's also worth nothing that PDO::quote() is implemented (or not) by each database driver. That means that the driver author can choose how to interpret this part of the documentation:

PDO::quote() places quotes around the input string (if required) and escapes special characters within the input string, using a quoting style appropriate to the underlying driver.

Some quick testing suggests that the MySQL driver always add such quotes but YMMV.

Comments