Select Problem with s possession

I have a select from database with a where condition in whish I have S possession and I faced with a problem. I want to know would somebody help me to solve this problem please. my code is :

string word= "Parent's House";
string str = "select count(*) from table1 where name = '" + word + "'";
OleDbCommand cmd = new OleDbCommand(str, cnn);
int x = (int)cmd.ExecuteScalar();
the select result is "select * from table where name = 'Parent's House' " and 3 ' character with each other and middle ' cause problem.
Please let me know how can solve this problem.
My Email address is {email address removed by moderator}
Thanks in advance
more ▼

asked Dec 30, 2010 at 02:28 PM in Default

SaeidHamzehee gravatar image

51 6 6 6

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The problem is that you have a value for word which contains a single quote, which is a string delimeter in SQL. Instead of getting into Escape operations in .NET you should instead use a parameterized query. Like this:

string word= "Parent's House";
string str = "select count(*) from table1 where name = @p1";
OleDbCommand cmd = new OleDbCommand(str, cnn);
int x = (int)cmd.ExecuteScalar();
That way ADO.NET will take care of shaping the string values so they become valid.
more ▼

answered Dec 30, 2010 at 02:51 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

You have identified the problem well and this is the very beginnings of a way of breaking into a website and its data, it is known as SQL Injection ( read this for an example - [http://unixwiz.net/techtips/sql-injection.html][1] ) and is (sadly) a very common method of breaking into SQL databases. It is recommended that you use a parameterised stored procedure to execute the SQL needed to fill your web pages. In the s. proc you can do the necessary checks for valid parameter values and not give anything away to someone who might want to break in. Magnus' suggestion of using a parameterised query is also valid providing you follow similar steps to mitigate unwanted attempts to break in.

[1]: http://unixwiz.net/techtips/sql-injection.html
more ▼

answered Jan 03, 2011 at 02:46 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left
As an alternative to using parameterised SQL, you could stick with the old-fashioned tried-and-trusted method of running every string value through a search & replace mechanism that replaces a single ' with '' (two 's)
more ▼

answered Dec 31, 2010 at 01:23 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

That's usually OK for string values. But not using parameters with ADO and ADO.NET creates more work than you probably have asked for.

If you always use parameters you'll know that your code is SQL Injection safe. If you don't, you'll have to be sure to check every piece of input from your application. I have too often seen code where ALMOST all input fields are checked.
Dec 31, 2010 at 03:10 AM Magnus Ahlkvist

These are all good points. You have to be careful.

Mind you, I remember working at one place where, instead of doing that, they put a keypress event on each input field to look for ' and replace with `. Which is wrong on so many levels...
Dec 31, 2010 at 03:20 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 30, 2010 at 02:28 PM

Seen: 973 times

Last Updated: Jan 03, 2011 at 02:30 AM