and Parameterised Queries aren’t susceptible to SQL Injection

Got a well deserved comment from Oren Eini (aka Ayende) on my post about ORM tools and their support for Stored Procedures. I sort of expected the performance comment as it is just becoming less and less an issue with each release of SQL Server and other DBMS.

I wasn’t 100% convinced about the SQL Injection Attack rebuttal and thought I had better check this out. Nothing convincing came up when I googled it. Next step, better verify this myself. I am pleased to report that using a paramaterised query is as safe as a stored procedure.

I executed three tests:

  • One calling a Stored procedure with a string parameter for the where clause.
  • The same SQL statement in a Paramterised Query
  • The same SQL with the parameter appended to it before executing

When I passed a string that looks like

Smith' AND 'X'='X

The Stored Procedure and the Parameterised Query correctly returned zero (0) rows.

When passed to the SQL statement I got the entire table returned.

Tested using .Net 2.0

Advertisements

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: