SQL, Microsoft SQl

I was trying to stress test my system's login unit. My system is designed like this -

if the user enters userid - abcd and password pass then the server takes these parameters and prepares an sql command and fires it to the microsoft database-

select password from UserInformationTable where userid = 'abcd';

The returned value is compared with the given password pass and result is then sent to the client.

I successfully broken into the system using the following method -

user enters userid - <abcd1 or drop table UserInformationTable >. This worked and my complete UserInformationTable got dropped.

Is there any graceful way of handling such a hacking problem. One way is to detect 'or' substring in the userid, but I did not find this very graceful. Is there any way I can restrict the no. of queries in a statement in microsoft sq开发者_StackOverflow社区l ?

Thanks and Regards, Radz

This is the SQL injection problem illustrated by the famous "Bobby Tables" cartoon.

Here is some info on how to fix it for MS SQL. Read especially @Rook's answer.

You have two problems.

1. You are subject to SQL injection attack as described by other users. Solve that problem by sanitizing your input and/or using parameterized queries.

2. You should neither store nor transmit plain text passwords. Why? See this story on Slashdot. The solution to this problem is to use one-way encryption to create a password hash to store in the database. When the user tries to log in use the same encryption on the password he or she provides, then search your database to see if a row exists with the same userid and password hash.

Use parameters in SQL queries. They automatically prevent this. Something like this in C#:

SqlCommand comm = new SqlCommand(conn);
comm.CommandText = "SELECT * FROM foo WHERE bar = @id";
comm.CommandType = CommandType.Text;

SqlParameter param = new SqlParameter("@id", DbType.Int64);
param.Value = 3; // The actual value that replaces @id

comm.Parameters.Add(param);

// ...

This not only applies to C#, but basically all modern DB systems and languages support parameterized queries <- google it.

And second, your first query can be changed to:

SELECT userid FROM users WHERE username = 'foo' AND password = 'bar'

Then just count how many rows you got returned, if it's 0, then the user entered the wrong password.