How does using parameters prevent SQL injection?
A lot of people are saying that using parameters instead of input strings (coming from for example, website's users) can prevent SQL injection.
But I don't get it, I mean how is "drop database" string different from "drop database" string entered into parameter, that is being used in the query?
Because simply entering "drop database" into the field on its own is not sufficient. You'd have to add some other characters to convince the SQL interpreter to terminate the previous statement and start a new one (to execute the DROP). Parameterisation will, among other things, prevent those kind of sequences from successfully being injected and interpreted in the intended manner by SQL. Characters such as ' for instance, which might close a string, will be escaped so that they are considered part of a variable, not part of the SQL.
Specifically, http://bobby-tables.com/about contains a worked example. In the example, there is some PHP code to insert a row into a table:
$sql = "INSERT INTO Students (Name) VALUES ('" . $studentName . "');";
execute_sql($sql);
If $studentName is set to something normal like "John", then the final SQL string produced by the code is benign:
INSERT INTO Students (Name) VALUES ('John');
Equally, similar to your example, if $studentName was set to "DROP TABLE Students", it still wouldn't have any effect. The final SQL would be:
INSERT INTO Students (Name) VALUES ('DROP TABLE Students');
No harm done.
But...if $studentName is set to something a bit more subtle, like this:
Robert'); DROP TABLE Students;--
The final string looks like this:
INSERT INTO Students (Name) VALUES ('Robert'); DROP TABLE Students;--');
This is then passed to the SQL engine, which interprets it as two statements (with, incidentally, a comment at the end) and executes them both, with unpleasant consequences.
However, if the value in $studentName had been passed as a parameter instead of just joined to a standard string, the final query would have ended up as
INSERT INTO Students (Name) VALUES ('Robert\'); DROP TABLE Students;--');
Notice the escaped ' in the middle, so it's now considered part of the string. It no longer causes the string to stop after "t".
Therefore what gets entered into the Name field in the table will be
Robert'); DROP TABLE Students;--
The DROP TABLE statement won't get executed because the SQL interpreter never sees it - it just treats it as part of the value to be added to the table.
Parameterisation means that anything within the parameter value is treated as a string (or possibly number) only - it can never escape outside that and be considered as part of the SQL statement itself.
Further reading:
How does the SQL injection from the "Bobby Tables" XKCD comic work?
might help you understand better.
SQL injection works by modifying the SQL query before the RDBMS parses the SQL syntax.
Query parameters are sent to the server after the SQL has already been parsed, so it's too late for a malicious value to affect the parsing of SQL syntax.
The query parameter can only be interpreted as a scalar value, like a string.
See an example on sql injection attack: Let's suppose this pseudo-code:
myfunction(myuser)
{
sql="SELECT user, password FROM users WHERE user='"+myuser+"'"
return db.execute(sql)
}
An attacker would exploit this vulnerability though this call to get all the passwords of the table:
myfunction("x' OR true OR 'a'='")
Instead, if the programmer executed the query through bind variables:
myfunction(myuser)
{
sql="SELECT user, password FROM users WHERE user=?"
sql.setParam(1, myUser)
return db.execute(sql)
}
In this case, it would be useless for the attacker to enter any tricky value as the previous one, because the database engine would treat the value 100% transparently, without admitting any control-characters within it.
