Getting data into and out of SQL tables with C#

Question

Getting data into and out of SQL tables with C#

I built a C# app that puts data into a SQL table.

I am currently using the SqlConnection class in .net. Got started with this article. http://www.codeproject.com/KB/database/sql_in_csharp.aspx

I had thought about looking at Linq to SQL but have not done it before and wasn't sure how to get up and going.

---

Well today I ran across a bug where I was trying to add data that include a "'" in it which broke my insert statement. I got to doing some research and am starting to wonder what other problems are lurking. What if I try to add the data "drop table"?

My question is, is there a better model for data insertion? Do I look at Linq? Or do I check all my data before it is entered?

Answer 1

You discovered SQL Injection Attacks. It is not good policy to just append user supplied data to a SQL query for exactly the reason you specified. Any user of your system could try to steal or corrupt your data by injecting some SQL.

The way to deal with it is described in the link, but basically you specify the parameters and let the supplied classes handle properly escaping the data so that if someone passes "Drop Table", it will simply be entered as data.

Here's a great example from CodeBetter.com

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";

command.Parameters.Add(
       new SqlParameter("@CustomerID", SqlDbType.NChar, 5)).Value = customerID;

Alternatively, feel free to use LINQ to SQL. It will handle this for you and is much easier to work with from a developer perspective. You can drag and drop your database into your code and it will completely map every table. Then you can write LINQ's version of SQL statements right in your code where you'll get code completion and Compile time checking for errors. This SO question will get you started.

Here's some simple LINQ code that lets you read a customer from the database, write his/her name to the screen, then update his personalized greeting (all safe from SQL injection):

Customer myCustomer = (
    from cust in myDatabase.Customers
    where cust.CustomerID == userPassedCustomerID
    select cust).Single();

Console.WriteLine(myCustomer.FullName);

myCustomer.PersonalizedGreeting = userPassedGreeting;
myDatabase.SubmitChanges();

Answer 2

The best way to work with database queries that need to be mingled with user input is to parameterized them. Now LINQ to SQL will do this for you but plain old ADO.NET lets you do it too (as the article I linked describes).

This works whether you are calling stored procedures or creating dynamic queries in your application to send to your RDBMS.

Answer 3

It appears you were doing some dynamic SQL, i.e. creating SQL statements on the fly, using various string manipulations in C# and then running these.

This way of doing is very versatile but introduces the risk of SQL injection. This was an accidental situation, self inflicted, but assuming that some of the elements used in building the SQL statement are provided by way of html fields, an malicious user could craft a particular string to effectively "DROP TABLE" or worse...

There are many ways to work around this situation, the most common one being to use parametrized SQL templates. With this technique, the variable parts of a SQL statement are provided to SQL in separate arguments (called parameter at the level of SQL). The ADO Command object is the vehicle used for invoking parametrized queries.

LINQ to SQL could also be used to handle this type of thing as well.