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
Tags: .Net
