Grrrr... grab this chance to share something that I learned before about SQL injection attack in desktop application environment.
Hmmm... why do I always have a chance to post?
What time is it now? Is it working time? ssstttt...
At the beginning, I didn't have courage to post this, because I scared this post is outdated already.
Nowadays, SQL injection attack is not a new trend, many people already knew how to prevent it in their application especially web application and website.
With this post, I will describe some concepts of SQL injection and how to prevent it.
Let's begin, no more roundtrip 
In this demonstration, I'll use MySQL Database Server and VB .NET application for the front-end.
Database: Northwind
Table: Employees
I assumed that these below 2 fields have been using for login to system.
FirstName acts as 'Username'
City acts as 'Password'
If you don't have this database, you can download it at here
For the front-end application, I am using VS 2008 but I already set the target framework of solution become '.Net Framework 2.0' due to I didn't use .Net Framework 3.5 features in this demo.
Ok, now we prepare a malicious SQL query.
Hmmm.. eg. '; INSERT INTO employees(FirstName,City) VALUES ('qiux','qiux') #
Yeah, We will use above query to inject northwind database.
Let's take a look below code.
Dim sUsername, sPassword As String
sUsername = Trim(txtUsername.Text)
sPassword = Trim(txtPassword.Text)
Dim sSQL As String = "SELECT FirstName, City FROM employees WHERE FirstName = '" & sUsername & "' AND City = '" & sPassword & "'"
Do you notice what's wrong with above query in variable sSQL? It looks nice rite? I can tell you, that's a bad practise EVER! Bad person or attacker will use it to inject your database.
Still don't believe? Well, take a look below explanation.
We input any string in textbox 'txtUsername' eg. ilovestudying or an empty string (depends on your luck ^^). And then we input this Whatever'; INSERT INTO employees(FirstName,City) VALUES ('qiux','qiux') # into textbox 'txtPassword'.
So after we run the application, we'll get 2 query in 1 line, the query will be like this SELECT FirstName, City FROM employees WHERE FirstName = 'ilovestudying' AND City = 'Whatever'; INSERT INTO employees(FirstName,City) VALUES ('qiux','qiux') #'
Take note of this character ; (semicolon), it tells SQL that you’re starting a new statement. Also this character #, this is a comment function in MySQL, attacker will use it to avoid the last character which in this case is ' (single quotation mark).
The approriate query will be like this,
SELECT FirstName, City FROM employees WHERE FirstName = 'ilovestudying' AND City = 'Whatever'; INSERT INTO employees(FirstName,City) VALUES ('qiux','qiux')
Look, you can insert a row with value 'qiux' for FirstName and 'qiux' for City. Now you gain the access to system, you can login into it.
How about 'DROP DATABASE' AND 'DROP TABLE' statements were using by attacker? Your data will be lost! Trivial mistake or practise can let you pay the high cost.
So how to prevent it? My suggestion is using parameter when you want to execute any SQL query or using a replacing function in .NET.
See below code,
Dim sSQL As String = "SELECT FirstName, City FROM employees WHERE FirstName = '" & sUsername & "' AND City = @Password"
And try again with this malicious query '; INSERT INTO employees(FirstName,City) VALUES ('qiux','qiux') #
You'll see the difference, SQL injection can't be done because we are using a parameter in query statement.
Secondly, we can use 'Replace' method to prevent it.
Private Function safeStringSQL(ByVal sInput As String) As String
Return sInput.Replace("'", "''")
End Function
I replaced this character ' to ''. Try to inject it, you won't success to inject it too.
If you still don't understand till here, that's okay. I already provided the source code for you to cook it ^^ Sometime we have to try it so we can understand it easily.
Below are some tips from me to limit the chance of exploit or injection:
1. When we run the application, an exception occurs in your catch block statement, don't ever to expose it especially the SQL errors raised by database to the user. Try to log error information and show to user only friendly information. This will prevent exposing unnecessary detail that could help an attacker.
eg. in this demo, I don't give a friendly information to user deliberately.
Because this is only a practise. Well, so what does above statement mean?
When we try to inject, at the beginning we don't know what's the table name used for login so attacker will try and try any table name.
Perhaps is '; INSERT INTO user(Username,Password) VALUES ('qiux','qiux') #
IF table 'user' doesn't exist in database, you'll get a message approximately like this, Table 'northwind.user' doesn't exist.
What happened? Attacker will know there isn't a table called 'user' and he/she will try another again, also apply to field name.
So we have to keep secret the SQL errors raised by database to the user.
You can try it later, try with whatever table name and field name.
2. Limiting the size and type of input. By limiting the size and type of input, you significantly reduce the potential for damage. For example, if your database lookup field is 20 characters long and comprised entirely of numeric characters, enforce it.
I know this is just a basic knowledge of SQL injection attack, but why not? This is an useful knowledge for us, rite?
Happy programming!
Source code
Note: Don't forget to change the connection string to MySQL Database Server!
Read more...