What is the proper time to use real_escape_string? When data arrives in POST, or just before composing the query?

Question

We know that to prevent SQL injection problems string values must be escaped before the SQL query is composed -- particularly those from users or other external sources.

When should this escaping be done? Should it be done as the value enters the program, storing the escaped value for later use? Or should store the unescaped value, and escape it just as the query is being composed? Which approach is safer? What is the tradeoff?

1) example of escaping as the value is received:

$test = $mysqli->real_escape_string($_POST['test']);
. 
. 
. 
$query=" UPDATE * from test_panel where test='" . $test . "'";

2) example of escaping as the query is composed:

$test = $_POST['test'];
. 
. 
. 
$query=" UPDATE * from test_panel where test='" . $mysqli->real_escape_string('$test') . "'";

Is there a difference between these approaches? Which approach is more prone to injections, and what is the safest method to prevent it?

Answer 1

That's quite interesting question but the answer is not that easy.

What is the proper time to use real_escape_string? When data arrives in POST, or just before composing the query?

NEITHER

Let me explain it a bit.

First, let's sort out the terminology. There are many mistakes in the way the question put in.

1. Let's talk not of escaping using real_escape_string but rather of formatting. Just because escaping has a very limited use - it's only a part of the formatting rules of just one type of SQL literals. While other types require different formatting rules.
2. Therefore, formatting when data "arrives in POST" is out of question - we just can't tell which field is going into which position in the query and thus we just don't know which rules to apply.
3. Last but not least: nor POST nor any other external source has absolutely nothing to do with query formatting. Once you have to put a string literal into query, you have to format it according to SQL syntax rules, no matter of it's source. Same goes for the numbers and such.

So, the only proper time when we have to format our data is right before the query composing.

Yet applying real_escape_string() right in the application code is a very bad practice.

1. As it was mentioned above, escaping is insufficient to format a string. String formatting involves both escaping and quoting. So, whatever facility intended to format strings for the SQL query, it should always perform both tasks, not one. Both quoting and escaping. Because these 2 rules are totally useless if applied one without another. So, it's essential to couple them together, in one facility.
2. Don't forget of different formatting rules for different data types. Numbers have to be cast to it's type explicitly, while escaping will do no good for them.
3. Manual escaping is just silly. Repeated $mysqli->real_escape_string('$test') makes your code bloated and hard to read. Why not to ask a database driver to do all the formatting for you? So, you have to follow the most modern technology - use a placeholder to represent data in the query. While processing such a placeholder, driver will automatically format the data going on it's place.
   And it will be either safe and convenient.

There are 2 methods of using placeholders easy way (without manual binding which is no better than manual escaping in terms of readability):

• Use PDO, as it lets you just pass a variable to be used in the prepared query

so, the code going to be

$db->prepare("SELECT * from test_panel where test=?");
$db->execute(array($_POST['test']));

and PDO will do all the formatting internally

• or invent your own wrapper to implement placeholders

like this one

function paraQuery()
{
    global $mysqli;

    $args  = func_get_args();
    $query = array_shift($args);
    $query = str_replace("%s","'%s'",$query); 

    foreach ($args as $key => $val)
    {
        $args[$key] = $mysqli->real_escape_string($val);
    }

    $query  = vsprintf($query, $args);
    $result = $mysqli->query($query);
    if (!$result)
    {
        throw new Exception($mysqli->error()." [$query]");
    }
    return $result;
}

$query  = "SELECT * FROM table where a=%s AND b LIKE %s LIMIT %d";
$result = paraQuery($query, $a, "%$b%", $limit);

or, for your current query:

$result = paraQuery("SELECT * from test_panel where test=%s", $_POST['test']);

look - it become short, sane and safe.

Answer 2

You should escape them as late as possible.

The reason you would want to do this is so that your data is always exact. For example, if you escape the string right at the start, the strlen won't be the same (as the unescaped version), which could cause confusion/errors in some scenarios.

---

The real (imo) answer to your question is just to forget about escaping and use prepared statements'

Answer 3

It doesn't matter wether you escape the input before or in the query.

And YES everything has to be escaped. There is no reason, why you don't want to escape your own things.

If you don't want to escape the strings, you will realize it ;-)

Answer 4

Values should never be escaped before actual use in composing the query. This is true whether you use a prepared statement/PDO or whether you use real_escape_string to compose the query as a SQL formatted string.

The practice of sanitizing/escaping a data value too early, and saving it in that form invites errors. If a variable contains a data value, like a customer name, or an account number, that variable should contain the raw value, un-escaped.

It is only when you actually form query, that should you make sure that all the values are properly encoded as they are put into that query.

Think of the variables holding raw data values as a different type of variable from the variables holding the queries. Never assign a query value directly to a raw data value, and never combine a raw data value to make a query. The composing of the query is the trigger to know that you should encode the raw data values.

By making this a practice, it will be clear and consistent where this encoding happens, you will reduce the potential of double encoding or failure to encode.

Imagine that you attempt to do the opposite: to pre-encode all values. This is not really possible since you have many many string values, and not all of them are to be used in queries. Somewhere, you are likely to have a variable that is used both as output to the display as well as used for a query. Displaying the escaped value would be incorrect. It is similarly difficult (or impossible) to track which variables are meant for use in a query, and which are meant for other, non-SQL uses.

Always store raw (un-escaped) values in all string variables until you actually compose a query. This practice is consistent with using prepared statements as well, since a prepared statement is passed an un-escaped value.

Answer 5

First of all you should pass the connection as the second parameter in mysqli real escape string Second of all you should also use prepared statements

http://php.net/manual/en/mysqli.prepare.php