Sign in to follow this  
JimboC

Trouble with INSERT INTO command (MS SQL + C#)

Recommended Posts

I'm having some additional problems writing to my MS SQL table through my C# program. I have a bunch of variables defined and I create the INSERT INTO command by creating a string with the data as shown in the following example:
Save_Data.CommandText = "INSERT INTO dbo.TableName (int1, int2, string1, string2) " + 
"VALUES ( " + int1 + ", " + int1 + ", '" + string1 + "', '" + string2 + "' )";
All's fine and dandy until some user comes along and uses an apostrophe in one of the strings then the whole command breaks down. Rotten users typing anything into the free text field. Grrrr I've been racking my brain and I can't think of another way to form that command. Probably because my brain is turning to mush from lack of sleep. Can anyone else come up with a way to write that command so that a user entering an ' doesn't throw a shoe? This is just a little program I'm writing for work that's probably only ever going to be seen by 10 people at the most. Would it be easier for me to just check the string and remove any apostrophes there? Kind of a poor work around I know, but it would get me through the problem.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
When you're using SQL Queries, never and i mean really NEVER build SQL Queries like that coz of the reasons you provided already...

look at using ADO.NET like below:

http://www.codersource.net/csharp_adonet_tutorial_ed.html

ADO.NET will secure your queries against sql injection as long as you follow those rules..

Also btw.. It's usuallu better by using Stored Procedures for any SQL Action because you can 1) control security a little easier/ better and 2) It allows you to fix things in the Stored procedures without re-deploying the entire new Code (as long as the bug is in the stored procedure that is). It's a different story if you want to build SQL Code which runs on multiple databases but still...

Apart from that there's also the added performance for stored procedures because their execution plan is optimized and stored.

You might even wanna look at Microsoft Enterprise Library and the Data Access Block (don't use it myself, because I have a custom built version for Stored Procedure Data Access)..

Also a lot of ppl have problems adding arrays as params for stored procedures and choose others ways because of it, if this is the case look into XML text strings ( ) Google for SQL OpenXML and i bet you will find enough info :)

Share this post


Link to post
Share on other sites
Use standard SQL escaping. Replace any single apostrophes in the users input with 2 apostrophes.

"Let's all go to the fair" becomes "Let''s all go to the fair"

Or you could use commands with parameters to get around parsing issues entirely.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Soz just re-read your post again.. Enterprise library is not really usefull for your project seeing the amount of ppl/ size of the project.. but still who knows it might be helpful for the future..

Gl!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this