Manually escaping in PHP/MySQL

Question

I try to understand manually escaping in PHP. I read this example:

    if ($_POST)
  {    
    $query = 'UPDATE 
                hersteller 
              SET
                zulieferer = \''.mysql_real_escape_string($_POST['zulieferer']).'\',
                telefon    = \''.mysql_real_escape_string($_POST['telefon']).'\',
                city        = \''.mysql_real_escape_string($_POST['telefax']).'\'
              WHERE 
                id = '.$_POST['id'];
    $update = mysql_query ($query) or die (mysql_error());
  }

The statement starts by an apostrophe. Unfortunately I couldn't find a discription.

1st part? 'UPDATE hersteller SET zulieferer = \'

2nd part? '.mysql_real_escape_string($_POST['zulieferer']).'

3rd part? ', telefon = \'

4th part? '.mysql_real_escape_string($_POST['telefon']).'

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Benjamin Gruenbaum, Mar 29 '13 at 13:46
You are looking for [string syntax](http://php.net/manual/en/language.types.string.php) and escaping, the [dot operator](http://php.net/manual/en/language.operators.string.php), and the cumbersome manual escaping function has a manpage of its own. — mario, Mar 29 '13 at 13:48

score 0 · Answer 1 · answered Mar 29 '13 at 13:47

The first part is this:

'UPDATE 
            hersteller 
          SET
            zulieferer = \''

note the extra '.

The escape character \ is used to escape the first ' so that it's inserted as a character into the string rather than interpreted as the end of the string.

It's similar to this:

$message = 'Let\'s get started';

$query = 'INSERT INTO table SET value = \'' . $value . '\'';

score 0 · Answer 2 · edited May 23 '17 at 12:22

A \ character marks the following character as "plain-text", or text that has no meaning. This way, your output would look like zuliefer = 'thevalue'.

For this example, if you don't like having to escape all the characters, you can start the variable with a double quote.

$query = "UPDATE 
        hersteller 
      SET
        zulieferer = '" . mysql_real_escape_string($_POST['zulieferer'])."',
        telefon    = '".mysql_real_escape_string($_POST['telefon'])."',
        city        = '".mysql_real_escape_string($_POST['telefax'])."'
      WHERE 
        id = '".$_POST['id'] . "'";
$update = mysql_query ($query) or die (mysql_error());

-More reading on single quotes and double quotes in PHP strings

-More reading on escaping

-PHP manual on strings and meanings of symbols inside them

Hazzit · Answer 3 · 2013-03-29T18:21:59.997

The code is a little hard to read for two reasons:

it uses single quotes within the SQL statement and for the php string
it is one long string spanning multiple lines

This might be easier to read:

$query = 'UPDATE '.
         '   hersteller '.
         'SET '.
         '   zulieferer = "'.mysql_real_escape_string($_POST['zulieferer']).'", '.
         '   telefon    = "'.mysql_real_escape_string($_POST['telefon']).'", '.
         '   city       = "'.mysql_real_escape_string($_POST['telefax']).'" '.
         ' WHERE  '.
         '   id = '.$_POST['id'];

Edit: Regarding your comment:

$query = "DELETE FROM description WHERE id = '" . $this->getId() . "'";

This serves the same purpose as my rewrite above, only exchanging the use of single and double quotes. It uses double quotes as php string delimiters and single quotes in SQL. Both variants are fine, since both PHP and MySQL allow both kinds of quotes. Your $query variable will actually contain DELETE FROM description WHERE id = '1234'. Please note, however, both versions are not syntactically identical. PHP handles double quotes a bit differently from single quotes. In double quotes, PHP will replace variable names with the contents of that variable, in single quotes it won't.

$query = "DELETE FROM description WHERE id='$id'"; <-- PHP will insert the variable $id
$query = 'DELETE FROM description WHERE id="$id"'; <-- PHP will not touch the string

clearly arranged. Learned a lot. Let me put a last question concerning this topic:
$qry = "DELETE FROM description WHERE id = '" . $this->getId() . "'";
The entire statement is surounded by quotation marks. The value is first surrounded bei dots, then by quotation marks and finally by an apostrophe? And if use that code style I don't need to escape, right? — user1662013, Mar 29 '13 at 18:04

score 0 · Answer 4 · edited May 23 '17 at 10:25

The statement starts by an apostrophe. Unfortunately I couldn't find a discription.

In PHP you can define strings with a pair of ' or a pair of ". The main difference lays in the fact that "-strings are also evaluated to seek for $ variables.

For both string types you can obviously escape the ' or " character in order to make it appear in the string:

echo "He said \"Welcome\".";
// He said "Welcome".

In your case, for example, assuming the POST variable to be equal to x:

'zulieferer = \''.mysql_real_escape_string($_POST['zulieferer']).'\''
// zulieferer = 'x'

Since that string doesn't use " I'd suggest you to refactor it to be:

$a = mysql_real_escape_string($_POST['zulieferer']);
$b = mysql_real_escape_string($_POST['telefon']);
$c = mysql_real_escape_string($_POST['telefax']);
$d = (int) $_POST['id'];
$query = "UPDATE hersteller 
          SET zulieferer = '$a', telefon = '$b', city = '$c'
          WHERE id = $d";

On a side note: you shouldn't use mysql_* functions. Use prepared statements instead.

That brings light at the end of the tunnel. Thanks a lot. – user1662013 Mar 29 '13 at 18:03 — user1662013, Mar 29 '13 at 18:03

Manually escaping in PHP/MySQL

4 Answers4