Johannes Schlüter

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 meant 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 it then requires resources. In a classic application this is no issue. The users starts the application up early in the morning and processes data multiple times so the prepared statement handle is re-used quite some time. The system benefits from early optimisations.  In a typical PHP Web application this isn't the case. A request and therefore a database connection with its associated 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 prepared 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 first have to build the list of place holders (the exact amount of place holders (?) separated by a comma, without trailing comma)  and then bind the values and mind the offsets when having other 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 dynamic 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 statements  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:

Modular software can be pain - you end up installing tons of add-opns. I recently configured a new desktop box and had to reconfigure Firefox. Here's the lsit of extensions I installed, I'd be interested in comments about better or missing add-ins

•  Adblock Plus
  This is a tricky one. I get content for free for looking at some ads. Might be a fair deal, but often it's too much. On some sites I allow ads, on others not.
• BetterPrivacy
  Prevent Flash Cookies from tracking me
• Brief
  An RSS/Atom/... Feed reader
• Certificate Patrol
  Notifies me when sites change their SSL certifactes, might prevent fraud.
• Change Rerer Button
  I don't like being tracked. At least not always
• CookieSafe
  Firefox's cookie handling is damn limited. With this add-in I can edit/delete single cookies way simpler and easily change the per site settings
• Firebug
  Good for fixing broken sites
• FoxyProxy Standard
  When switching between company VPN and my local net I need different proxy configuration. With this extension I don'T have to go through the settings
• Greasemonkey
  Improve websites according to my interest
• JSON View
  Renders json more nicely. Good when working with JSON-based protocols
• NoScript
  per-site configuration of javascript and stuff. Some websites have nice JS free versions.
• Open in Browser
  Adds a new option to the download dialog to open in Browser as web page or plain text. Useful for files a server sends with a "wrong" header.
• Tab Kit
  The wider the screen the more I want to have the tabs on the side. This add-in shows tabs in a sidebar with a tree structure and different colors and such
• Tamper Data
  Edit HTTP Request before the browser sends them, initially I've got this for security research, nowadays I use it to work-around issues in bad WEb-UIs
• User Agent Switcher
  Maybe switching the User agent every now and then makes tracking a bit harder, certainly it can be nice to get a different view. Many sites have a mobile version which is focussed on the content way mor than the regular site.
• Web Developer
  Nice tool for working with HTML stuff