News Archive
PhpRiot Newsletter
Your Email Address:

More information

Escaping from the statement mess

Note: This article was originally published at Planet PHP on 18 May 2011.
Planet PHP

One of the issues Web Developers face is making their application robust to prevent SQL injection attacks. Different approaches exist which help. Sometimes people use large abstraction layers (which, sometimes, don't make anything safe ...) and sometimes people use prepared statements as a way to secure queries. Now prepared statements were a nice invention some 30 years ago abut they weren't ment for making things secure and so they do have some shortcomings: One issue is that preparing and executing a query adds a round-trip to the server where i then requires resources. In a classic application this is no issue. Tee users starts the application up early in the morning and processes data multiple times so the parepared statement handle is re-used quite some time. The system benefits from early optimisations.A In a typical PHP Web application this isn'T the case. A request and therefore a database connection with its sassociated statement handles lives way less than a second before being thrown away. The PDO MySQL driver, by default, tries to improve that by emulating the prepared statement on the client side. This emulation faces issues as it is lacking the knowledge of what's valid SQL which can lead to strange behaviour (The simple example is $pdo-prepare("SELECT * FROM t LIMIT ?")-execute(array($_GET['count'])); which will emit an SQL syntax error) and inherits limitations from prepared statements. A second issue with preapred statements is that queries are being built dynamically. A common case which is hard to do with prepared statements is the IN() clause with a dynamic amount of values. With prepared statements you fisrt have to build the list of place holders (the exact amount of place holders (?) separated by a comma, without trailing comma)A and then bind the values and mind the offsets when having tother values - this typically becomes ugly code.

So why not take a step back. - Let's not try to emulate prepared statements but try to make it simpler to construct queries while escaping data?

An API for doing this might follow the sprintf() semantics and look like this;

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE f1 = %s AND f2 = %i", "foobar", 23);

which would return a string

SELECT * FROM t WHERE fi = 'foobar' AND f2 = 23

which can safely be send to the database. As said the IN clause should work. as we're in PHP we might simply extend it to do this:

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE f1 IN (%s)", array("foobar", 23)); SELECT * FROM t WHERE f1 IN ('foobar', '23')

Well doesn't look fancy? - But there's more: By not pretending to emulate prepared statements we can easily work with more danymic queries. Something along the lines of

$sql = mysqli_format_query($mysqli, "SELECT * FROM t WHERE uid = %i", $_SESSION['uid']); if (isset($option['option1']) { $sql .= mysqli_format_query($mysqli, "AND option1 = %s", $option['option1']); } if (isset($option['option2']) { $sql .= mysqli_format_query($mysqli, "OR option2 = %s", $option['option2']); }

Doing such a thing using prepared statementsA or in some classic way becomes way harder to maintain. For playing with this approach I quickly cooked up a simple implementation of that logic which should work well with PHP 5.3 and mysqli:

Continue reading "Escaping from the statement mess"