From what do sql parameters protect you?

Question

Parameters are used to protect you from malicious user input.

But if the parameter expects a string, is it possible to write input that will be interpreted as sql, so malicious users can use things like 'DROP', 'TRUNCATE', etc...?

Are there differences in protection between parameters in asp, asp.net, java and others?

---

See also: Are parameters really enough to prevent SQL injections?

Answer 1

Not as a parameter. SQL injection relies on concatenating malicious code into an SQL string then executing the SQL statement from that string. A prepared statement takes parameters, regardless of the content. With a prepared statement the actual text of the SQL statement itself is never changed.

Answer 2

The only risk would be if you perform an exec on a parameterized string.

In all other cases, parameterized queries are safe.

Answer 3

Parameterized queries generally quote the parameter if it is a string behind the scene so that the normal SQL operators are not interpreted as such. This means that even if a user enters potentially malicious data it is simply treated as a string input and not interpreted as SQL operators/commands.

There may be technical differences in how it is implemented in the various frameworks, but the basic idea (and result) is the same.

Answer 4

No. SQL injection attacks can occur from any language in any SQL DB. The kind of attack you are referring to is when a programmer uses dynamic SQL in their source like 'USER_NAME = sName' and the user can enter unlimited text for user name so they can append a comment and then type any new SQL statements such as 'DROP', 'TRUNCATE', etc.

Answer 5

Nothing you input as a parameter via a BindWhatever() call can ever be executed as SQL.

The SQL has already been parsed and evaluated before you bind ht evariable data so its simply impossable for this data to get mistaken for SQL.

Of course someone could still pass you some JavaScript when the database will faithfully store and possibly serve up for execution on someone elses browser!

So you still need to rid your input (or at least escape) any ({[]})\ type characters;

Answer 6

You need to be careful with your definitions. 'Parameters' can mean a number of things; parameters to a stored procedure, for example, don't protect you at all in and of themselves. To use Java as an example:

sql = "exec proc_SearchForUser '" + userNameToSearch + "'";

is no better or worse than the raw

sql = "SELECT * FROM Users WHERE userName = '" + userNameToSearch + "'";

and is just as susceptible to the username

';DROP TABLE users;--

Parameterized queries, on the other hand, ARE safe. They might look like

PreparedStatement statement = con.prepareStatement("SELECT * FROM Users WHERE userName = ?");

or indeed

PreparedStatement statement = con.prepareStatement("exec proc_SearchForUser ?");

The reason this is safe is because when you fill in the value... using, say,

statement.setString(1, userName);

then the string -- even one like "';DROP TABLE users;--" -- will be properly escaped by the DB engine and rendered innocuous.

It's still possible to screw it up -- for example, if your stored procedure just builds a SQL string internally and executes it, trusting the input -- but prepared statements with parameters mean that no unescaped data will ever get to the DB server, completely cutting off that attack vector.

Answer 7

See my question here: http://stackoverflow.com/questions/306668/are-parameters-really-enough-to-prevent-sql-injections for an interesting discussion on sql parameters.