Filtering character ' in Login Form

Hello guys,
Here is an interesting problem to tackle with. Most of us are familier with SQL Injection. For those who are not Familier here is Short intro to it.

When you enter your data into a Login Form, suppose ID and Password, Following code comes into action.

"SELECT * FROM User_Master WHERE Username ='" + txtUsername.Text + "' and " + "Password ='" + txtPassword.Text + "'"

So If I Enter Following Inputs
Username = John
Password = John123

The Resulting SQL Querry becomes

SELECT * FROM User_Master WHERE Username ='John' and Password ='John123'

Suppose I Enter chatacter ' into Username, then let's see what happens.

SELECT * FROM User_Master WHERE Username ='' ' and Password ='John123'

Now, as you can see the clause Username = '' ' causes rest of the code to be neglected. The resulting Query Crashes.

So Let's Filter it out.
Suggestions are Welcome.
Have a nice day and Thanks in Advance.

[Reverend Jim]

Read Avoiding SQL Injection Attacks. It explains what parameterized queries are and how to use them.

[tinstaafl]

Before you call the SELECT command you can use a simple conditional statment.

If txtUsername.Text.Contains("'") Then
    'Tell the user the mistake
    MessageBox.Show("Username cannot contain a '")
    'Erase the bad username
    txtUsername.Text=""
    'Return back to the form and await more input
    Return
End If

I would suggest kicking it back to the user. If the user mad one mistake, typing a ', maybe he or she made more in typing the user name. You could also use a password confirmation textbox if the 2 don't agree kick it back

[G_Waddell]

Hi
I would suggest using the keypress event of the login name to only allow letters (Upper and Lower case,) and the numbers 0-9 and some function keys (e.g. backspace, delete and space) that way they will only be able to input valid characters into the login box and correct any error they made.. If it is a webpage login you could do something similar with client side Javascript or use Ajax...

[Reverend Jim]

But what if the username does contain a ' and it is valid, like O'Neal? In that case a parameterized query will handle it or you can use

txtUserName.Text.Replace("'","'').

[G_Waddell]

True, you could allow ' if you wanted to but I suspect that as you are designing the log in form etc. you are probably in charge of what constitutes a username i.e. you could insist on ONeal or have them not be actual names e.g. JN234

The other reason I was thinking of limiting the input like this is to block the user entering any extended SQL character that they could use deliberately or inadvertantly to cause problems. ' was given as an example but ; for example can also be used to cause merry hell on a SQL query.

Fellas suggest us what all characters can cause SQL query to crash .

[Reverend Jim]

I suspect that as you are designing the log in form etc. you are probably in charge of what constitutes a username

But a single quote is a valid character in a name and because the example given was "John" I assume that actual names are being used. Using parameterized queries addresses the problem os embedded single quotes. A semi-colon is not a character that appears in a name.

[adam_k]

If you are not checking for strings or if you don't handle the single quote then you are in danger with much more than "special" characters.

To make things clear:

SELECT * FROM User_Master WHERE Username ='123' union all select DB_NAME() --  ' and Password ='John123'

and on the second run use the value returned by DB_NAME() to:

SELECT * FROM User_Master WHERE Username ='123' go DROP DATABASE database_name go  --  ' and Password ='John123'

For those who will complain that union requires same amount of columns from both selects, there are ways to get around it.

You can't protect yourself from SQL injection just by filtering single quotes or special characters or by using parameterized queries.
If you are serious about it, then you've got to use stored procedures - with input validation and limit the user the app uses to connect to the absolutely required stored procedures and nothing else.

[Reverend Jim]

According to

• Tech Forums
• //TODO: Ramblings of a software development enthusiast
• Stack Overflow
• forums.asp.net

parameterized queries DO prevent SQL injection attacks.

[G_Waddell]

Hi Guys,
The way I look at my apps from a security perspective is the more layers of security the harder it is to crack. So generally what I do is the following (Not saying this is what you must do, just what I do)

Layer 1 form input, block any characters except A to Z, a to z and 0 to 9 also only allow space delete and backspace etc. ( of course you can let in other characters if that suits)

Layer 2 Code that processes the input filters out unwanted character from input as well (This is in case they some how get past layer 1)

Layer 3 Data Access Layer; I place all data interactions with in a Class with set functions to retrieve data and only call stored procedures - this means I can reuse common data functions/ opearations and I don't create queries on the fly which leads me on to layer 4.

Layer 4 backend database; As I said, I avoid creating queries on the fly and use stored procedures I use a dedicated DB user for my app whos rights are limited to Execute permission on the stored procedures I need. That way even if an injection attack got through they would not have the right to drop or modify tables or database.

[adam_k]

@ReverendJim, I don't disagree that parameterized queries DO prevent injections, but WILL they continue to? What I mean is that we used to think that QUOTENAME function was there to protect us from SQL injections as well, but then somebody discovered that you can overflow it and bypass it.
If we are talking about a low-risk app, then yes probably you can use parameterized queries and get away with it, but if you were building a banks system or paypal wouldn't you want to secure it in each and every level?
We don't know each and possible vulnerability the tools we use have - or not until somebody cracks them.
That's in mission critical systems, where SQL injection is a hazard then I suggest an approach like G_Waddell's, but with a few changes:
Layer1 - Agree, but should also limit the length of each field to the expected length of input. Avoids overflow
Layer2 - Agree
Layer3 - It doesn't necessarily have to be a separate class, but work with stored procedures is a must, as is parameterized queries (See Reverend, we weren't that far apart, it's just that it won't do that much on it's own)
Layer4 - Use db security (permissions), limit access to server to the IPs that need to and use data validation inside the sproc. Preferably use active directory accounts with Trusted Connection.

I've heard of all checks being performed and then the app connected to the db with sa username/password.

In general, each layer has to validate the data it has been passed from the previous layer - as if it were passed from user input. This way even if somebody disassembles your exe your db will stop the attack.

Then you have a system that you can consider relatively safe and have to follow up with new cracking techniques/vulnerabilities.

[Reverend Jim]

I don't disagree that parameterized queries DO prevent injections, but WILL they continue to?

Who knows? But that argument can be used for any security. We know anti-virus software protects us now but will it continue to do so? We know that firewalls protect us now but will they continue to do so?

We don't know each and possible vulnerability the tools we use have - or not until somebody cracks them.

That's the reason we don't develop vaccines for strains of flu that haven't shown up yet. Doesn't mean we shouldn't use the vaccines we have now. If we try to write software to guard against every conceivable threat (and those still not thought of) then the software becomes too expensive and too complex and takes far too long to develop.

Having said that I have two further comments. I got a flu shot last September and still got the flu and a nasty case of bronchitis. Bummer. Also, I am not advocating taking no precautions. I'm just saying we take all reasonable precautions. For most people asking questions on this forum I think parameterized queries fit the bill. Based on the perceived level of expertise of the posters (no insult intended) I suspect that if they don't know how properly phrase more than a simple query then complex field validations, stored procedures and database/domain permissions are probably beyond them.

Also, when I was a dbadmin/developer for our control centre I use complex field validations, stored procedures and database/domain permissions (AFAIK there were no parameterized queries at that time). But then again, I had a few decades of programming under my belt by that time.

[adam_k]

I agree, but if you train a newbie that if this the way to go, they might take your word for granted (after all you are a moderator in a well-respected forum, with decades in programming) and go make something stupid.
We wouldn't be having this "conversation" if you said "If you have to ask, then start with the best protection for your level and use parameterized queries". Let the guy know that he isn't set for good if he uses this type of query.

Recently I've been to a warehouse that had a state of the art WMS, that I am guessing used a bunch of checks to prevent injections. Unfortunatelly the Wifi for the RF guns was unlocked, the PC client for the WMS was mentioning the IP of the server in the status bar ("Connected to xxx.xxx.xx.xxx") and there was no password for sa.
I know the guys there, so I discussed this with the IT guy, who assured me that my data (and my goods) were safe, because he tried SQL injection and couldn't get it done. He couldn't believe how fast it got open.

[Reverend Jim]

If you have to ask, then start with the best protection for your level and use parameterized queries". Let the guy know that he isn't set for good if he uses this type of query.

You have to learn the basics before you get fancy but I think your suggestion is quite reasonable. It's a good idea to let them know there are more robust alternatives but for now they can...

And I'll add that having decades of experience doesn't make me an authority. Far from it. A lot of that experience is in very old technology. Or as my brother-in-law once said to a pompous uncle (much to the delight of his grown sons), "just because you're old doesn't mean you're smart".

[G_Waddell]

Hi guys,
I hope we didn't scare Rahul47 away!