Blogging Tips

ASP: avoid SQL injections

If you work a lot with ASP and SQL, you might already know about SQL injections.
It's quite clear that every time we offer a visitor the possibility of filling a form (whatever its purpose may be), there's a security risk for our database. Basically, an attacker can insert some code in a form element, which will serve as a breach, allowing access to data stored in the database.
How that's done is not the main topic of this short post, however we should be aware of the fact that those threats are often used to update, delete and insert data, or in worst cases, they are used in order to gain access to reserved areas of a web site.
In this article, we are going to create a small VBScript function to avoid SQL injections.

Just for reference, I would like you to consider the following SQL query:

query = "SELECT * FROM users WHERE username= '" & name & "';"

The above query is selecting all the data from a users table, where the username is equal to a submitted variable (name). It is a commonly used query for a login form.
If an attacker fills the form with something like:

' or '1'='1

the query will be:

query = "SELECT * FROM users WHERE username ='' or '1'='1';

As you may notice the query will work and produce as result a valid user (because 1 is always equal to 1). Can you see how easily security could be breached?

The solution is very simple. We basically need to filter the form data, before using it in our query. To do so, we just need to replace some common characters used in SQL injections.
So, we create a small function that will do that.

<%
Function CleanText(subText) 
  If Len(subText) > 0 Then
   subText= Replace(subText,"'","''")
   subText= Replace(subText, "*", "[*]")
   subText= Replace(subText, "%", "[%]")
  End If
CleanText=subText
End Function
%>

I don't think there much to say about the CleanText function. It replaces ', * and % which are the commonly used characters in SQL injections. We can use the function just before using variables in a query. Following the above query example, our variable is "name", so we clean it up before passing it to the query:

<%name = CleanText(Request.QueryString("name"))%>

And that's all for today!