I've copied ~www/php/include/sql.inc.php to sqli.inc.php, and converted many of the functions to use prepared statements, and renamed them from sql_* to sqli_*.

Background

Until now, we have always simply put SQL queries together, like

$query = "update profiles set email = '" . $email . "' where username = '" . $user . "'";

and run it with

sql_query($databaseName, $query);

This could be bad (at least theoretically) if somehow somebody managed to set $email to have a value of

evil_person@scary_place.com'; --

The apostrophe and semi-colon finish the query statement, and the two hyphens make everything after it just a comment, so everybody's email address gets set to the evil person's address.

New functions

The new version of

sql_query($dbName, $query)

sqli_query($dbName, $query, $types, $params)

$query now has question marks as placeholders for the parameters:

"update profiles set email = ? where username = ?"

$params is an array of values to replace the question marks in the query:

array('allenh@eecs.berkeley.edu', 'allenh')

$types is a string of one-letter type designations for the parameter values:

"ss"

since both parameters are strings. The possible type designation characters are

s     string

i     integer

d     double

b     blob, to be sent in packets (not sure what this means)

s	string
i	integer
d	double
b	blob, to be sent in packets (not sure what this means)

The new sqli_query() uses a prepared statement that treats the SQL separate from the parameter values, so no bogus parameter value can be treated as SQL. This protects us from this kind of attack.

Here are the current replacements for commonly used functions in our code. They all open & close their own database connections (unless you pass in one of your own), and also internally open & close their own prepared statements internally:

sql_query ($db, $query, $userLinkID=0)
sqli_query ($db, $query, $types=NULL, $paramValues=NULL, $userMysqli=NULL)

sql_select ($db, $query, $userLinkID=0)
sqli_select ($db, $query, $userMysqli=NULL)

sql_select_field ($db, $field, $query, $userLinkID=0)
sqli_select_field ($db, $field, $query, $types=NULL, $params=NULL, $userMysqli=NULL)

sql_select_row ($db, $query, $userLinkID=0)
sqli_select_row ($db, $query, $types=NULL, $params=NULL, $userMysqli=NULL)

sql_select_array ($db, $key, $query, $userLinkID=0) {
sqli_select_array ($db, $key, $query, $types=NULL, $paramValues=NULL, $userMysqli=NULL)

You can also create your own prepared statement and then use it in a loop to make multiple queries with different parameters. Here is a somewhat silly example: Where we used to say,

    foreach ($listOfUsernames as $username) {
        $query = "select email from profiles where username = '${username}'";
        echo $username . " has email address "
            . sql_select_field($dbName, 'email', $query)
            . "<br>n";
    }

... you can now say,

    $mysqli = sqli_open($dbName);
    $query = "select email from profiles where username = ?";
    $statement = sqli_open_statement($mysqli, $query);
    foreach ($listOfUsernames as $username) {
        echo $username . " has email address "
            . sqli_prepared_select_field($statement, 's', array($username))
            . "<br>n";
    }
    sqli_close_statement($statement);
    sqli_close($mysqli);

This is much more efficient than calling sqli_select_field() inside the loop, which would open and close a database connection and a prepared statement on each iteration.

This last example illustrates that it's the caller's responsibility to make sure that the prepared statement and the connection get closed, and it skirts error handling altogether. PHP supports exceptions and has a try/catch construct. But it has no finally, which would be nice for making sure the statement and connection get closed even if there are errors. We might be able to catch all contingencies with exceptions and try/catch. I haven't looked into it yet.