TableAdapters SQL Injection

Question

Hi I am using a dataset and in that dataset I have a table adapter. In my table adapters I have used stored procedures as queries. If I use the following lines to insert form data using my table adapter, is it safe against SQL injection? Thanks.

UserDataSetTableAdapters.UserInformationTableAdapter myFactory = new TestProject.UserDataSetTableAdapters.UserInformationTableAdapter();
            myFactory.spTest_InsertUserInformation(id, frmAddress);

Answer 1

short answer: Yes :)

Update 1: Even if you didn't use stored procedure and defined queries with parameters on the adapter, it would be safe against sql injection i.e. select f1, f2 where f3 = @myparameter ... that would use a prepared query.

Answer 2

Verify using SQL Prolier tracing. If the underlying API is safe, you'll see parameterized T-SQL commands, something along the line of sp_executesql.

Answer 3

Without posting your stored-procedure code, there's no way to truly answer your question, but you can probably answer it yourself.

---

SQL injection attacks stem from user-entered data wiggling their way into dynamically-generated and executed SQL queries. Using a stored procedure generally avoids this problem by passing the arguments as parameters, thus not dynamically generating SQL. Procedures are automatically encapsulated and do not become part of your original SQL query text.

Take the following for example:

SELECT *
FROM myTable
WHERE myId = @ID;

As a parameter, you're safe to set @ID to "21; DROP TABLE myTable;". It will get escaped for you and the entire string will be compared to myId. However, if you dynamically generate your SQL query like

string query = "SELECT *\nFROM myTable\nWHERE myId = " + userEnteredText + ";";

Now you'd get the following:

SELECT *
FROM myTable
WHERE myId = 21; DROP TABLE myTable;;

Ouch.

---

So, to answer your question: IF your stored procedure doesn't dynamically generate SQL based on its parameters and EXEC them, you should be safe.

Note: This, of course, relies on your .NET data provider to be calling the procedure with parameters and not generating dynamic SQL statements. Most do this correctly, but if you're using a 3rd party provider, you should double-check this before assuming you're safe.

Answer 4

If you are using dynamic SQL in the proc itself which uses EXEC (@SQL) and not sp_executesql with parameters then you are not safe, otherwise you are