SQL injection, using Parameterised Queries

Question

I have some questions about how to prevent sql injectiion with the help of parameterised queries

sqlQuery="SELECT * FROM usersTbl WHERE username=@uname AND password=@passwd";
SqlCommand cmd = new SqlCommand(sqlQuery, conn);

SqlParameter[] par = new MySqlParameter[2];

par[0] = new SqlParameter("@uname ", SqlDbType.VarChar,25);
par[1] = new SqlParameter("@passwd", SqlDbType.VarChar, 45);

And then I attach them to the SqlCommand and ExecuteScalar it.

For example the client insert the string ;DROP -- in the password variable, will the parameterised query prevent the DROP query to be executed ?

Thank you

John Woo · Accepted Answer · 2013-03-23 17:25:33Z

4

Of course, when the client pass ';DROP -- value in the password field, this will be parsed into

SELECT * 
FROM usersTbl 
WHERE username=@uname AND password=''';DROP --'

The command object will automatically escapes any single quotes found on the value.

UPDATE 1

As, I already told you, it won't. Because the quotes will be escaped by doubling the quotes. Example,

string pass_val = "'; DROP usersTbl;--";

when you passed that into command and its value is parameterized, this will become

SELECT * FROM usersTbl WHERE ... AND password='''; DROP usersTbl;--'

and NOT

SELECT * FROM usersTbl WHERE ... AND password=''; DROP usersTbl;--

edited Mar 23, 2013 at 17:25

answered Mar 23, 2013 at 17:00

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yogev Over a year ago

SELECT * FROM usersTbl WHERE username=@uname AND password=''; DROP usersTbl;-- So the @passwd parameter is ''; DROP usersTbl;--. Will it execute it? because the #passwd is VarChar(string)

Matthew Over a year ago

Parametrized queries isn't the same as escaping query values. When using parametrized queries, the parameters and the actual query are two distinct parts.

Yogev Over a year ago

So it there any way to bypass it and make the DROP(or other SQL commands) to be executed?

John Woo Over a year ago

@user1501674 hmmm are you trying to get anyway to by pass parameterized queries? unfornately, no.

Yogev Over a year ago

So what you are telling me is that as long as I'm using parameterised queries I'm utterly protected from SQL injections ?

Chad · Accepted Answer · 2013-03-23 17:00:05Z

1

Yes, the parameterized query will correctly escape any characters that would allow this to happen.

answered Mar 23, 2013 at 17:00

Chad

7,5772 gold badges26 silver badges34 bronze badges

Collectives™ on Stack Overflow

SQL injection, using Parameterised Queries

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related