Now I’ll talking about a technique that script kiddies widely used to attack to the first wall of your application. If you’re a rookie for security topics on development then you may never heard about this before. In my .NET courses training experiences, most of my trainees never know about this issue before and they feel very surprises when I’ve hacked into their system in no time.
What is SQL injection?
Straightly, It’s something like you try to inject some unexpected characters into SQL querying process to gain the out-of-case result.
Let’s see it in more detail!
What should you do If you want to coding your application to authenticate user’s credential that kept in database?
So easy, right? I’m just querying the result from database with this simple SQL query and a few line of code.
Dim strSQL As String = “Select COUNT(*) From Members WHERE LoginName=’” & txtLoginName.Text & “‘ AND Password=’” & txtPassword.Text & “‘”
Dim cmd As New SqlCommand(strSQL, con)
It works perfectly! but how the it’ll handle if a hard core user input something unexpected into login name just like the following
xyz’ OR ’1′=’1
When it concatenate into SQL string. It’ll result in to…
Select COUNT(*) From Members
WHERE LoginName=’xyz’ OR ’1′=’1‘ AND Password=’1234‘
Yeah, you can see that OR ’1′=’1′ which always result in TRUE. So, the hard core user can authenticate to the application without knowing of any user’s login name or password.
How can I prevent SQL injection?
Yeah, it’s very easy to do. Just use the technique named as “Parameterized Query”.
OMG! What’s about it? I never heard about those “Parameterized Query”.
You get me in trouble again!
Not that serious, It’s very easy to implement this technique as .NET alraedy provide the framework for you. Just do the following two steps.
1. When you want to create the dynamic SQL query string just like this case. You should use parameter instead of concatenate the variables yourself.
Select COUNT(*) From Members WHERE LoginName=@LoginName AND Password=@Pwd
We call @LoginName and @Pwd as parameter.
2. Before executing the command. Please specify the value for each parameter first.
When the command was executed. All parameters will be transformed into the value that suitable for the data type of those database. The good things you get here is that. For string (varchar) data type, generally it should open and close with single quote ” ‘ “. (You can see this code of the first code block) But not for parameterized query, as it will do automatically internal. So, you don’t have to pay your attention to those data type symbol for each database. (Especially datetime data type) and another point, the generated SQL query will never been attacked by SQL injection anymore as it know now how to handle those type of technique.
This is all about that!
For more information about SQL injection, please visit here.