Preventing SQL injection with stored procedures [LWN.net]

Preventing SQL injection with stored procedures

Posted Jun 1, 2006 3:53 UTC (Thu) by mrshiny (subscriber, #4266)
Parent article: SQL injection vulnerabilities in PostgreSQL

I'm curious as to how using stored procedures prevents SQL injection? In my experience you can create a call to a stored procedures just like a call to SQL:

String query = "{ call some_package.somefunc('" + arg + "'); }";
CallableStatement cs = connection.prepareCall(query);
cs.execute();

The above is just as vulnerable to sql injection as any normal SQL statement. Am I missing something?

(Log in to post comments)

Preventing SQL injection with stored procedures

Posted Jun 1, 2006 4:29 UTC (Thu) by xoddam (subscriber, #2322) [Link]

Your example prepares the procedure using untrusted input, so the
prepared query itself is untrustworthy.

Preparing stored queries without using untrusted input requires the use
of placeholders for the arguments, and passing the input *later* when the
query is executed. Then no untrusted input will ever be parsed as SQL so
there is no injection vulnerability.

Preventing SQL injection with stored procedures

Posted Jun 1, 2006 9:39 UTC (Thu) by nix (subscriber, #2304) [Link]

It's also more efficient to use prepared queries and (I think) easier to read.

So you win on all fronts.

Preventing SQL injection with stored procedures

Posted Jun 1, 2006 13:18 UTC (Thu) by mrshiny (subscriber, #4266) [Link]

Right, place-holders in the prepared query will prevent injection attacks. But the same functionality is available for normal queries, so I guess I'm not seeing how "using stored procedures" is advisable. Really it comes down to "use prepared queries/procedure-calls". A developer who doesn't understand "use prepared queries" can't be trusted to make the leap to prepared queries for procedure calls.

Preventing SQL injection with stored procedures

Posted Sep 27, 2007 18:50 UTC (Thu) by einhverfr (guest, #44407) [Link]

I know this is an old post, but there is one area where this can make a difference.

The stored procedure idea does not prevent sql injection. However, in combination with appropriate db permissions it could be used to arbitrarily restrict what a user can do in the database. If the db is locked down well enough, and all access is through stored procs, and if you use db native accounts, you may not have to worry about sql injection attacks in the application in the same way you would otherwise.

There are, however, two big caveat to this issue. If user-supplied input is used to create the stored procedure name, this could be exploitable as well. The second is that not all queries are parameterizable inside stored procedures on all databases. Hence you could have SQL injection *inside* your stored procedures. In some cases, you have just moved the issues of SQL injection tracking back into the stored procs.