The Soulpass Project

A major concern when developing database-driven websites is SQL Injection. Poorly written code could result in a malicious user running custom queries from his or her browser! Obviously the effects of that sort of security hole could be devasting for a web application.

Since I deal mostly with PHP/MySQL/Oracle, I’ll just be talking about PHP/MySQL/Oracle.

PHP has a built-in MySQL function called mysql_real_escape_string(). If that’s the injection-prevent method you go with, any time user-provided data from a URL query string is going into a database query it should be passed through that function.

If you’re using Oracle, the best and most elegant bet is to take advantage of the query binding features.

An example from the Wikipedia article on SQL injection (modified to fit the Oracle syntax):

$query = $sql->prepare("select * from users where name = :NAME");
$query->execute($user_name);

The execute member function here takes the value of $user_name and “binds” it to “:NAME” in the query. In other words it replaces the data stored in $user_name with the bind variable “:NAME.” However, before dumping it into the query it makes sure that the data in $user_name is not SQL. Regardless of the contents it treats it as query-safe data. When using native Oracle binds there is no need to use other functions such as mysql_real_escape_string().

This entry was posted on Monday, March 5th, 2007 at 6:50 pm and is filed under Database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.