Subscribe to Thought Particles        RSS Feed
-----

SQL Injection and why you should care

Icon 10 Comments
Most anyone who has been in the VB.Net forum has seen me from time to time posting about using parameters in your sql statements instead of building the string from the user input devices. (e.g., concatenating the string with the variables)

I'm not going to repeat the examples of parameter usage that already appear on this site but I want to take the time to explain why when we talk about using them, we're not just being overly cautious. The biggest response I get from people is "Well, my program isn't web based, it's all local or used on our internal network". The reply I have to that is, if you are asking for information, and you put that information into a Where clause, you are vulnerable.

Here is a perfect example that I see a lot of people using and I want to use it to show why you should care about SQL-Injection.

Someone submits an SQL statement comprised of the User and Password to gain access to the program. They use the following SQL statement.
Dim sql as String = "SELECT AccountNo FROM Users WHERE UserName = '" & txtUser.Text & "' AND Password = '" & txtPassword.Text & "'"


OK, seems simple enough right. After all, if the user doesn't enter the correct UserName and Password, no row is returned which means the login failed.

So CharlieMay, Just exactly how does this affect me?

Well, let's look at how SQL is working in this scenario.

Let's say you entered CharlieMay in txtUser and SmurfBerries in txtPassword. Your sql statement would look like:
SELECT AccountNo FROM Users WHERE UserName = 'CharlieMay' AND Password = 'SmurfBerries'


Of course, this is completely valid and if the password wasn't SmurfBerries, then again, no row is returned and the login failed.

OK, here is where it gets scary.

In most SQL engines, -- is seen as a comment. So, anything that appears after it is ignored.

So what if our SQL statement above was just
"SELECT AccountNo FROM Users Where UserName = 'CharlieMay'"
Yep, you guessed it, it would return a row leading you to believe the login was successful. And of course it was because you met the criteria of the statement, you found a row with the UserName equal to 'CharlieMay'
But...
Did you know that you can produce that same sql statement using the first Statement I wrote, simply by changing it in the textbox?

Let's see how. Remember the comment, well, let's just comment out the rest of the statement.

So in the txtUserName we'll enter:
CharlieMay'-- (notice the apostrophe (') and the --)
and for the password we'll enter:
WhoKnows (we really don't care what get's entered here and you'll see why)

So with that, what does the first SQL statement now look like? Let's see...
SELECT AccountNo FROM Users Where UserName = 'CharlieMay'--' AND Password = 'WhoKnows'

Do you see the problem, EVERYTHING after the -- is considered a comment so this is what your sql engine sees
SELECT AccountNo FROM Users Where UserName = 'CharlieMay'

And of course the row is returned and any other columns that may be checked are there.

OK, so there you have it. If it's not clear, feel free to comment. This is my first attempt at Blogging so I'm sure I can improve.


10 Comments On This Entry

Page 1 of 1

JackOfAllTrades Icon

22 January 2011 - 07:32 AM
Excellent entry, CharlieMay. I've featured so that hopefully more will find it!
0

CharlieMay Icon

23 January 2011 - 06:24 PM
Thanks :D
0

polens Icon

17 February 2011 - 09:31 AM
wow sir this is cool and educating will try not to that in the future!!
0

polens Icon

17 February 2011 - 09:33 AM
Holy $#@# i tried it on my project and it works OMG!!!
what will i do?
0

CharlieMay Icon

17 February 2011 - 06:56 PM
Polens, you can use parameters in your statement that will control the ability to do this.

Example:

Dim sStmt as string = "SELECT * FROM Users WHERE UserName = @username"
dim cmd as As SQLCommand = New SQLCommand(sStmt, Connection)
cmd.Parameters.AddWithValue("@username", textbox1.text)
cmd.ExecuteNonQuery


So what is happening here is that the parameter is handling the value being used in the SQL statement and will ensure that it is safe prior to executing against the database. As such this also helps when people enter user names like O'Donald. When appending this to an SQL statement your Query would look like:
SELECT * FROM Users WHERE UserName = 'O'Donald' which of course would produce an error in the statement because of the extra apostrophe.
3

hardygandhi Icon

14 March 2011 - 05:07 AM

CharlieMay, on 18 February 2011 - 07:26 AM, said:

Polens, you can use parameters in your statement that will control the ability to do this.

Example:

Dim sStmt as string = "SELECT * FROM Users WHERE UserName = @username"
dim cmd as As SQLCommand = New SQLCommand(sStmt, Connection)
cmd.Parameters.AddWithValue("@username", textbox1.text)
cmd.ExecuteNonQuery


So what is happening here is that the parameter is handling the value being used in the SQL statement and will ensure that it is safe prior to executing against the database. As such this also helps when people enter user names like O'Donald. When appending this to an SQL statement your Query would look like:
SELECT * FROM Users WHERE UserName = 'O'Donald' which of course would produce an error in the statement because of the extra apostrophe.

great job sir can u plese tell me how to store date and time with this text file???
thank you ...
0

CharlieMay Icon

24 March 2012 - 02:58 PM

hardygandhi, on 14 March 2011 - 08:07 AM, said:

great job sir can u plese tell me how to store date and time with this text file???
thank you ...

I'm not sure what you mean by "this text file" but if you have a question on programming beyond what this is talking about then please submit your question with the code you're using to the appropriate forum. We'll be glad to answer it
0

christian Mukeba Icon

08 May 2013 - 10:49 PM
This is great and very educative......tx CharlieMay you a star
0

Adqusit Icon

12 February 2014 - 09:33 AM
Hello charlieMay, I didn't understand on one thing that you said -- uses for comments, that's right. You said that:

Quote

So in the txtUserName we'll enter:
CharlieMay'-- (notice the apostrophe (') and the --)
and for the password we'll enter:


Why you are using -- here. In the first query
Dim sql as String = "SELECT AccountNo FROM Users WHERE UserName = '" & txtUser.Text & "' AND Password = '" & txtPassword.Text & "'" 
you didn't use -- it at all.
0

CharlieMay Icon

06 March 2014 - 07:42 AM
Adqusit, that query is a concatenated statement that takes the user's input from txtUser.Text and txtPassword.Text and builds the string.

If txtUser.Text had Adqusit in it and txtPassword.Text = Wx@b1zy!q then sql would contain:

SELECT AccountNo FROM Users WHERE UserName = 'Adqusit' AND Password = 'Wx@b1zy!q'

Since you are only appending what the user enters what stops them from entering an injection?

So If the user enters Adqusit'-- in the txtUser textbox and LetMeIn in the txtPassword textbox then sql would contain:
SELECT AccountNo FROM Users WHERE UserName = 'Adqusit'--' AND Password = 'LetMeIn'

Since -- marks the rest of the line as a comment, your user has just converted your sql statement to
SELECT AccountNo FROM User WHERE UserName = 'Adqusit'

Notice, they just took out the check for a valid password.

I should note that Access does not use the -- so this wouldn't work there but there are still ways of doing this in Access that I won't go into details on. I will say, it's just as easy though.
0
Page 1 of 1

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

July 2014

S M T W T F S
  12345
6789101112
13141516171819
20212223 24 2526
2728293031  

Tags

    Recent Entries

    Search My Blog

    0 user(s) viewing

    0 Guests
    0 member(s)
    0 anonymous member(s)

    Categories