How does stored procedure prevents SQL injection?

Question

May be I am not much clear about the stored procedure. Can someone explain me how stored procedure prevents SQL injection with a simple example using MySql.

Answer 1

Stored procedures are a form of parameterised query. The fundamental problem that causes SQL injection is data being treated as query language.

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

In this example, if I set $password to foo' OR 'x'='x, we get this:

SELECT * FROM users WHERE username = 'blah' AND password = 'foo' OR 'x'='x'

Since the character 'x' is always equal to the character 'x', this query will always return rows regardless of whether the user / pass is correct. The database can't know that you didn't intend this, because it's only being given a string with no context.

In order to prevent this, you have to be able to know the difference between the query and the data. Stored procedures solve this problem by writing the query beforehand, with markers for parameters, so that data can be passed into them later. For example:

SELECT * FROM users WHERE username = ? AND password = ?

The database driver sends the name of this stored procedure (or, in standard parameterised queries, just the query text itself) and a list of parameters, as distinct separate entities in the protocol. This means that the database server can parse the query string as query language safely, and treat parameters solely as data, without any ambiguity.

I also wrote a longer answer a while back that explains all of this in a more verbose way, if that's any use to you.

Answer 2

Stored procedures are not immune to SQL injection. As explained here:

https://blogs.msdn.microsoft.com/brian_swan/2011/02/16/do-stored-procedures-protect-against-sql-injection/

So long as dynamic SQL can be created inside the stored procedures, you are vulnerable to SQL injection.

And MySQL as of 5.0.13 onwards, have dynamic SQL capability:

https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure

And thus is vulnerable to SQL injection.

In SQL server, here is an example:

http://www.sqlinjection.net/advanced/stored-procedure/

VULNERABLE STORED PROCEDURE USING EXEC STATEMENT.
CREATE PROCEDURE getDescription
   @vname VARCHAR(50)
AS
   EXEC('SELECT description FROM products WHERE name = '''+@vname+ '''')
RETURN

And another example:

VULNERABLE STORED PROCEDURE USING DYNAMIC CURSOR.
CREATE PROCEDURE printDescriptions
   @vname             VARCHAR(100)
AS
   DECLARE @vdesc VARCHAR(1000)
   DECLARE @vsql VARCHAR(4000)
   SET @vsql = 'SELECT description FROM products WHERE name='''+@vname+''''
   DECLARE cur CURSOR FOR EXEC @vsql

   OPEN cur
   FETCH NEXT FROM cur INTO @vdesc

   WHILE @@FETCH_STATUS = 0
   BEGIN
      PRINT @vdesc
      FETCH NEXT FROM cur INTO @vdesc
   END

   CLOSE cur
   DEALLOCATE cur
RETURN

And in Oracle, here are the examples:

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-oracle-database-code

https://www.blackhat.com/presentations/bh-europe-04/bh-eu-04-litchfield.pdf

The above presentation by David Litchfield in Blackhat Europe has something more serious, from SQL injection it can lead to privilege escalation - so a normal Oracle user can be running as a DBA if the stored procedures are created by DBA (eg, all Oracle system objects).

Answer 3

A SQL database works a statement in several steps. At first the test of the SQL statement is parsed, after that it will be optimized and compiled. When this is finished the database has now a internal piece of software that can run the given SQL statement.

Stored procedures are pre compiled. In other words the database creates that internal piece of software before you use it. In this case only programm code is interpreted without any influence of parameters.

If you pass a full SQL statement inclusive parameter to the database it process the above described steps.

For instance ...

SELECT * FROM myTable WHERE id=1

or you give something like that ...

SELECT * FROM myTable WHERE id=1;DROP TABLE myTable

Normally nobody would write a statement like the second one in his programm code, but if you take for instance direct parameters from a web request, it is possible that a statement like that results.

var sqlString="SELECT * FROM myTable WHERE id=";
sqlString = sqlString+request.getParameter("id");
// database parse, compile and optimize
var result=database.doQuery(sqlString);

If you use Stored Procedures or Prepared Statement. The parse and compile process is allready done in database. All the interpretation depends on your programm code. When you call it the database only insert the given parameters to the precompiled code and it nows the eccepted datatypes for it.

var sqlString = "call queryMyTable(?)";
// get the precompiled statement from database
var statement = database.createStatement(sqlString);
// inject the parameter
statement.setParameter(1,request.getParameter("id"));
// if 'id' is a number it works fine ...
// but if 'id' is '1;DROP TABLE myTable' you will got a type cast error and the risk of SQL injection is banned
var result = statement.execute();

Stored procedures and prepared statements are in view of the security equal.

Answer 4

Another way to think about it, to be explicit and augment the already given answers.

SELECT * FROM users WHERE username = 'blah' AND password = 'foo' OR 'x'='x'

Without the prepared statement the OR after the 'foo' is treated as code

Now, with the prepared statement from the perspective of the database the password tried is:

'foo' OR 'x'='x'

I.E the OR (and everything else) after 'foo' is treated as data