MOCKSTACKS
EN
Questions And Answers

More Tutorials









SQLServer SQL Injection

SQL injection sample


Assuming the call to your web application's login handler looks like this:

https://somepage.com/ajax/login.ashx?username=admin&password=123

Now in login.ashx, you read these values:

strUserName = getHttpsRequestParameterString("username");
strPassword = getHttpsRequestParameterString("password");

and query your database to determine whether a user with that password exists.

So you construct an SQL query string:

txtSQL = "SELECT * FROM Users WHERE username = '" + strUserName + "' AND password = '"+ strPassword
+"'";

This will work if the username and password do not contain a quote.

However, if one of the parameters does contain a quote, the SQL that gets sent to the database will look like this:

-- strUserName = "d'Alambert";
txtSQL = "SELECT * FROM Users WHERE username = 'd'Alambert' AND password = '123'";

This will result in a syntax error, because the quote after the d in d'Alambert ends the SQL string.

You could correct this by escaping quotes in username and password, e.g.:

strUserName = strUserName.Replace("'", "''");
strPassword = strPassword.Replace("'", "''");

However, it's more appropriate to use parameters:

cmd.CommandText = "SELECT * FROM Users WHERE username = @username AND password = @password";
cmd.Parameters.Add("@username", strUserName);
cmd.Parameters.Add("@password", strPassword);

If you do not use parameters, and forget to replace quote in even one of the values, then a malicious user (aka hacker) can use this to execute SQL commands on your database.

For example, if an attacker is evil, he/she will set the password to

lol'; DROP DATABASE master; --

and then the SQL will look like this:

"SELECT * FROM Users WHERE username = 'somebody' AND password = 'lol'; DROP DATABASE master; --'";

Unfortunately for you, this is valid SQL, and the DB will execute this!

This type of exploit is called an SQL injection.

There are many other things a malicious user could do, such as stealing every user's email address, steal everyone's
password, steal credit card numbers, steal any amount of data in your database, etc.

This is why you always need to escape your strings.

And the fact that you'll invariably forget to do so sooner or later is exactly why you should use parameters.Because
if you use parameters, then your programming language framework will do any necessary escaping for you.

simple injection sample


If the SQL statement is constructed like this:

SQL = "SELECT * FROM Users WHERE username = '" + user + "' AND password ='" + pw + "'";
db.execute(SQL);

Then a hacker could retrieve your data by giving a password like pw' or '1'='1; the resulting SQL statement will be:

SELECT * FROM Users WHERE username = 'somebody' AND password ='pw' or '1'='1'

This one will pass the password check for all rows in the Users table because '1'='1' is always true.

To prevent this, use SQL parameters:

SQL = "SELECT * FROM Users WHERE username = ? AND password = ?";
db.execute(SQL, [user, pw]);



Conclusion

In this page (written and validated by ) you learned about SQLServer SQL Injection . What's Next? If you are interested in completing SQLServer tutorial, we encourage you simply to start here: SQLServer Tutorial.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.