Monday, 24 May 2010

Stored Procedures do not necessarily prevent SQL Injection

It seems that a lot of people think that just because an application uses stored procedures, it's queries must be safe. This absolutely false. Stored Procedures do not inherently add security, as they can be put together as poorly as any dynamically built query. I saw a perfect example of this the other day. An application took inputs, passed them to a stored procedures which then built a sql query by concatenating the inputs with predefined query strings. It then called sp_executesql to execute the dynamic query. The developer obviously had heard that stored procedures were safer than dynamic queries, so they went and made an SP, but they had their SP build a dynamic query. So all they succeeded in doing was pushing the problem back into the database layer instead of the app itself.

So testers and developers, please do not assume that an sp means safe. you still have to properly parameterize your queries and validate input and output. Security and shortcuts do not go together. If you think you may have vulnerable SPs like this, try running a query such as SELECT object_Name(id) FROM syscomments WHERE UPPER(text) LIKE  '%SP_EXECUTESQL%' OR UPPER(text) LIKE  '%EXECUTE%' OR UPPER(text) LIKE  '%EXEC%'
 to try and see where these venerabilities are.

No comments:

Post a Comment