php - Mysqli query auto-escaping

I was just thinking about this today and it really amazed me. Why hasn't anyone created an auto-escaping algorithm for mysql queries in php? It isn't that hard, is there any good reasons why people are not doing this?

Side note: I know there are prepared statements, but my question is regarding the cases where you don't feel like using such or something doesn't allow you to. Or even before prepared statements came along, still I hadn't seen such practice?

For example, instead of escaping data before adding it to the query, add raw data to the query method and then inside it:

public function query($query){

    preg_match_all('\'(.+?)\'',$query, $m); // just an example
    // perform escaping on matches and build the escaped $query
    parent::query($query);
}

Answer

They did: http://www.php.net/magic_quotes

This feature turned out to be dumb. It was deprecated in PHP 5.3 and removed from PHP 5.4. See that page for explanations of why it was removed.

For example, instead of escaping data before adding it to the query, add raw data to the query method and then inside it...

Your example fails if quote marks appear in the query text, but not as string delimiters.

SELECT * FROM movie_quotes WHERE text = 'I can''t stand him.';

There are also known cases where addslashes() and regular expressions fail for certain character sets. The escaping function must be character-set aware. That's why there's a special function for it in each database API.

Just use query parameters. There is no reason not to. It's easier than using escaping. It's just as secure as escaping, if not more so.

Query parameters can be used only for values like string literals, date literals, and numeric literals. Parameters cannot take the place of dynamic table names, column names, SQL expressions, keywords, etc. But the same is true of escaping.

For those other cases, use whitelisting.

...if you're using an older version of mysql where prepared statements are not available...

If you're still using ext/mysql, it doesn't support query parameters, but it is officially deprecated and will be removed in a future version of PHP. If you are still producing code with ext/mysql, you will be unable to upgrade when that happens.

So you should switch to ext/mysqli or PDO.

• Mysqli has been available since PHP 5.0.0, released 2004-07-13.




• PDO has been available since PHP 5.1.0, released 2005-11-24.

If you're still using a version of PHP older than that, it's way past time to upgrade. PHP 4 has been officially end of life since 2007-12-31, and even critical bug fixes stopped in 2008-08-08.

Newer versions of PHP are generally faster than older versions, as well as adding new features and fixing many bugs -- including critical security bugs. Don't use a ten-year-old version of PHP.

or not using mysql at all?

All brands of RDBMS support query parameters.

In addition to PDO drivers, many brands of RDBMS have their own PHP extension, analogous to the mysqli extension. For example oci8 for Oracle. These extensions support prepared statements, e.g. oci8_parse().

pdo simulates prepared statements for other databases but doesn't really work as a prepared statement

This is not true. PDO supports real prepared statements in many drivers. There are options to emulate prepares as well, so you can use every driver with either positional parameters (?) and named parameters (:param), even if the RDBMS support only one style or the other.