SQL Injection attack – what can be done?

An older site that I web master has had a pretty nasty SQL injection attack (appears to have hit 500 000 sites also). As a quick solution to stop this happening again, a simple thing to do is create a function  something like this:


function cleanSQL($sql) {
//replace any occurence of ; — <  >  SCRIPT INSERT DELETE UPDATE
$sql = str_replace(“;”,”;”,$sql);
$sql = str_replace(“‘”,”‘”,$sql);
$sql = str_replace(“<“,”<“,$sql);
$sql = str_replace(“>”,”>”,$sql);
$sql = str_replace(“–“,”–“,$sql);
$sql = str_replace(“SELECT”,”SELECT”,$sql);
$sql = str_replace(“DELETE”,”DELETE”,$sql);
$sql = str_replace(“UPDATE”,”UPDATE”,$sql);
$sql = str_replace(“INSERT”,”INSERT”,$sql);
$sql = str_replace(“SCRIPT”,”SCRIPT”,$sql);
$sql = str_replace(“select”,”SELECT”,$sql);
$sql = str_replace(“delete”,”DELETE”,$sql);
$sql = str_replace(“update”,”UPDATE”,$sql);
$sql = str_replace(“insert”,”INSERT”,$sql);
$sql = str_replace(“script”,”SCRIPT”,$sql);
$sql = str_replace(“=”,”=”,$sql);
//sql = replace(sql,”@”,”@”)

return $sql;

Or in classic ASP:

function cleanSQL(sql)
‘//replace any occurence of ; — <  >  SCRIPT INSERT DELETE UPDATE
sql = replace(sql,”;”,”;”)
sql = replace(sql,”‘”,”‘”)
sql = replace(sql,”<“,”<“)
sql = replace(sql,”>”,”>”)
sql = replace(sql,”–“,”–“)
sql = replace(sql,”SELECT”,”SELECT”)
sql = replace(sql,”DELETE”,”DELETE”)
sql = replace(sql,”UPDATE”,”UPDATE”)
sql = replace(sql,”INSERT”,”INSERT”)
sql = replace(sql,”SCRIPT”,”SCRIPT”)
sql = replace(sql,”select”,”SELECT”)
sql = replace(sql,”delete”,”DELETE”)
sql = replace(sql,”update”,”UPDATE”)
sql = replace(sql,”insert”,”INSERT”)
sql = replace(sql,”script”,”SCRIPT”)
sql = replace(sql,”=”,”=”)
‘sql = replace(sql,”@”,”@”)

cleanSQL = sql
end function

So where ever you are doing something like:

$SQL = “SELECT * FROM USERS WHERE USERNAME = ‘” . $username . “‘ AND PASSWORD = ‘” . $password . “‘;”;

clean the $username and $password first:

$username = cleanSQL($username);

If you use SQL Server, it is probably wise to use stored procedures and pass parameters into this.

When ever any free text ends up being used within a dynamically generated SQL statement, use the cleanSQL() function to clean it up. You may have to think about what sort of data is going in of course as the above function is converting certain text items to html escape codes.


About this entry