White Board Security Question #1

Are Store Procedures (SP) SQL injectable?

Answer:

SP protects from SQL injection when SQL statements are executed independently from the input parameters. The same applies to prepared statements. When I say "independently" I am addressing the fact that the input is not embedded in the SQL statement but passed as parameter to a precompiled SP. SQL injection is possible in store procedures when the EXEC statement is embedded in the SP to execute a SQL statement that is passed as input parameter. Such input parameter can be a concatenated string that could be altered with user input. The same applies to prepared statements used badly such as when still concatenate inputs before being executed.

For example, let say you have a SP such as

CREATE PROCEDURE dbo.doQuery(@query nchar(128))
AS
Exec(@query)
RETURN

this SP can execute whenever malicious input is passed to the SP as a parameter.

As a best practice you should avoid to execute SQL statements internally to a store procedure and instead call the execute externally and passing the input query parameters through a parametrized query.

For example, this is a way to call a store procedure with parametrized input parameters:

string str ="sp_doQuery";
cmd= new SqlCommand(str, sqlConn);
cmd.CommandType= CommandType.StoreProcedure;
cmd.Parameters.Add("param 1" p1);
cmd.Connection.Open();
SqlDataReader read= cmd.ExecuteReader();

In this case the parameters cannot alter the syntax of the query statement embedded in the store procedure.

Assuming that you indeed for some reason you still want to execute the sql statement passed as parameters to the SP you might want al least to check the input internally with a regex such as:

CREATE PROCEDURE dbo.query.doQuery(@is nchar(4))
AS
DECLARE @query nchar(64)
IF RTRIM(@id) LIKE ‘[0-9][0-9][0-9][0-9]’
BEGIN
SELECT @query =’select ccnm from cust where id =’’’ +@id +’’’’
EXEC @query
END
RETURN

SP can offer an additional protection from SQL injection because you can set up user permissions to run the SP and prevent execution from untrused code. As a best practice you want the application to run minimum privilege (for example not use sa account to run queries).

From the security stand point this add ianother layer of security even if adds some admin overhead (e.g. set permissions to each SP in declarative way).

I would say that if you stick with use of SP with prepared statements and parametrized queries (no concatenation of input parameters) and if you do input validation of input parameters inside and outside the SP you are in good shape on mitigating SQL injection with SP.