Escaping quotes in PHP

Question

How do I escape quotes in PHP when trying to query a MySQL database?

Without adding addslashes on every value:

$fname = addslashes("Value's with quote''s'");
$lname = addslashes("Value's with quote''s'");

Answer 1

The proper way is using prepared statements, e.g. via PDO.

If you can't do that, you have to process all values which are passed into a database query with mysql_real_escape_string() - and no, doing that simply on all $_POST data is not an option since that would render them unusable for HTML output, etc. You could create a $_ESC or something similar though... but note that this variable will not be superglobal!

Answer 2

You ought to escape special characters (not only quotes) on every string value (it's useless to escape values you're not going to enclose in quotes in a query. Those values require another treatment).

To avoid boring repetitive typing you can apply an escaping function to array items in a loop.

In case you're using MySQL and for INSERT/UPDATE queries, you can use this helper function:

function dbSet($fields) {
  $set = '';
  foreach ($fields as $field) {
    if (isset($_POST[$field])) {
      $set .= "`$field`='" . mysql_real_escape_string($_POST[$field]) . "', ";
    }
  }
  return substr($set, 0, -2); 
}

It is used like this:

$id     = intval($_POST['id']);
$table  = 'users';
$fields = explode(" ","name surname lastname address zip fax phone");
$query  = "UPDATE `$table` SET ".dbSet($fields).", `date`=NOW() WHERE id=$id";

Also don't forget to set proper encoding using mysql_set_charset() as it's required for the mysql_real_escape_string() function.

Answer 3

A good idea would be using PDO prepared statements as described here.

It will automatically escape those characters.

Answer 4

Firstly, don't use addslashes() - it is not recommended for use with escaping DB query strings because it doesn't escape everything that actually needs to be escaped; there are some characters that can still get through.

The correct solution depends on the database you're using. Assuming you're using MySQL, the correct function to use instead of addslashes() is mysql_real_escape_string().

You probably notice that using this on every line is even more verbose than addslashes(), so it doesn't really answer your question.

If your fields are all separate variables (as per your example), then you're really stuck with doing that for a bunch of lines of code.

If you're using an array (eg $_POST), then you can do it in a loop, which will make things a lot neater - you can do things like this:

foreach($_POST as $key=>$value) {
    $sqlstrings[$key]="`".$key"` = '".mysql_real_escape_string($value)."'";
}
$sql = "update table ".implode(' ',$sqlstrings)." where id=".$update_id;

A more up-to-date method for doing SQL is to use an object model rather than manually building the queries. PHP has a number of libraries that may help: mysqli is an improved MySQL library, and PDO is a database-neutral library. Either of these would give you much better security and flexibility than building the SQL code directly. However if you already have a lot of code in place then they would represent a fairly significant overhead of code changes, so you may want to go with the mysql_real_escape_string() option discussed above in the short term. I do recommend investating them them though.