asked
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 + "'"; cnn.Opent(); OleDbCommand cmd = new OleDbCommand(str, cnn); int x = (int)cmd.ExecuteScalar(); cnn.Close(); 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
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"; cnn.Opent(); OleDbCommand cmd = new OleDbCommand(str, cnn); OleDbCommand.Parameters.AddWithValue("@p1",word); int x = (int)cmd.ExecuteScalar(); cnn.Close(); That way
ADO.NET will take care of shaping the string values so they become valid.
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)
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.
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...
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
