SQL injection concatenation vs parameter

Question

I have been reading an article on sql injection attack and in there, they have been saying that this statement is prone to injection attack:

"SELECT * from tblBlah where userId" +userId

but this query is not"

"SELECT * from tblBlah where userId = @userId";

i am trying to find an explaination on why that is the case. they both are are expecting a parameter. could it be the first query can accept a parameter from URL and second cannot?

Answer 1

In the case of the first query someone could pass the following value for userId:

 = 3; DELETE FROM tblBlah;

This would be string concatenated and would result in the following SQL statement:

SELECT * from tblBlah where userId= 3; DELETE FROM tblBlah;

Of course, this would be catastrophic for your database.

I think in the case of the second query, the database would compile it internally to be a single SELECT statement. The value of the parameter would be inserted where the placeholder is, but only as data. Even if we tried the following assignment:

@userId = '= 3; DELETE FROM tblBlah;';

we would end up with the following query:

SELECT * from tblBlah where userId = '= 3; DELETE FROM tblBlah;';

In other words, we tried to inject code but all we were really able to do was inject a string parameter. This could cause a bad query to fire, but it would not allow a malicious user to call DELETE. In fact, the user would have zero control over which statement executes.

This is a simple example demonstrating the power of prepared statements. With prepared statements, the general template or structure of the query is already compiled before the query actually runs. Portions of the statement have parameter placeholders, which however do not alter the query through concatenation, but only by assigning positioned values.