Blog :: How did I miss this?

Ok, PHP has a long running reputation as a language with security issues. This is not, I would argue, due to the language itself (which has been relatively secure) but because the barrier to entry is so low (compared to EJB or even Perl), hence lots of relatively unskilled programmers write web applications. Lower programmer skill leads to more security holes and a generally bad reputation.

PHP as a language and environment does, however, bear a little responsibility for the lingering stigma. Let's not mention "register globals" or magic_quotes_gpc, instead lets look at how sql queries typically get built. I have, I confess, written code that looks like this:

$sql = 'update mytable set col1="' . addslashes($val1) . '", col2="' . addslashes($val2) 
         . '" where id=' . (int)$id;
mysql_query($sql);

I know, the horror, the horror! String concatenation and very rudimentary protection against sql injection attacks. The code readability suffers immensely if there are more columns to update and addslashes isn't actually the correct function to use to fight sql injection attacks (what if you are using MS-SQL instead of mysql, for instance).

So now I use the DB package from pear. It provides a quoteSmart() member function that will quote a literal value in the manner appropriate to the database driver being used and enclose it in quotes (if necessary). I still have been using string concatenation to build my sql statements though. I've gotten a little more advanced, but my code might now look like this:

$vals = array($value1, $value2, $value3);
$vals = array_map(array($db, 'quoteSmart'), $vals);
$sql = 'insert into table2(col1,col2,col3) values(' . join(',', $vals) . ')';
$db->query($sql);

This is an improvement. At least I'm not worrying about lots of string concatenation and I don't have quotes embedded in quotes. Still, two extra function calls... It's too bad that php/mysql doesn't support a cleaner variable binding style syntax. Except: It does, almost. No bound variables with mysql, but it does support placeholders. From now on in my code the same statement will look like

$sql = 'insert into table2(col1,col2,col3) values(?,?,?)';
$db->query($sql, array($value1,$value2,$value3);

That's right. It's in the docs (though not apparently widely known. How ever did I miss this?) The query function accepts two parameters and does substitution and quoting on the values of the second parameter. I haven't ever seen this style in sample code, somehow, yet I consider myself to be an elite php coder and a widely read one at that. Ah well, something new every day. (Hat tip Cortana at /.)

Posted on Aug 3rd 2005, 11:01 AM