's problem when insert into database

Dear Friends,

I have a textbox in my form and I want to save the text that is inputed in the text box to database. But the problem is - when a text is inputed in the text box that contains 's (invited comma) then a error is occured because of 's.
Please help me.....

Rajib

An apostrophe is a special character and you cannot insert it into the database without some manuplation. The easiest would be to replace the single Apostrophe with two apostrophes, however I would not recommend that.

The better approach is to use parametrized quesries. Parametrized queries are faster and safer compared to trivial SQL that is built on the fly. Take a look at how parameterized queries are built on MSDN documentation. You should read the documentation about the SQLCommand/OleDBCommand/ODBCCommand objects which ever you are using.

Take for e.g. the following SQL statement:


Table Student with 2 columns: Id and Name...


string Sid = ' S3 ';
string SName = ' John ';
String sql = " Insert into Student(Id, Name) values (' " +Sid+ " ', ' " +SName+ " ') ";

The string that will be stored within the sql string above is:

Insert into Student(Id, Name) values ('S3 ', 'John')


...which when you execute will insert 'S3' into column Id, and 'John' into column Name


Now the same example, but take the following situation:


string Sid = ' S3 ';
string SName = ' Mc'Brian ';
String sql = " Insert into Student(Id, Name) values (' " +Sid+ " ', ' " +SName+ " ') ";

The string that will be stored within the sql string above is:

Insert into Student(Id, Name) values ('S3 ', 'Mc'Brian')


If you look at the above text that will be stored within the sql string, when executed will generate an error since Mc'Brain contains the single quote ' that will actually execute and end the text input. To solve this, you need to replace the tick ' in Mc'Brian with something else that will not perform the action of the singel quote.

What I do is use a string replace function. Thus pass through SName to this function, and replace it with either a space or a \'.... the \ is the escape character e.g.

SName = SName.Replace(" ' ", " \' ");

Thus when SName will now store: Mc\'Brian and the sql statement will read:

Insert into Student(Id, Name) values ('S3 ', 'Mc\'Brian')


The escape character will prevent the single quote from ending the text string.

I strongly recommend that you DON'T use the String.Replace function. That is simply creating a workaround for a problem that doesn't even exist. If everyone did the right thing from the word go and used parameters then all these questions about data types and quotes would go away. In the above exapmle you SHOULD do this:

VB Code:

mySqlCommand.CommandText = "INSERT INTO Student (ID, Name) VALUES (@ID, @Name)"
mySqlCommand.Parameters.AddWithValue("@ID", id)
mySqlCommand.Parameters.AddWithValue("@Name", name)

That's the "proper" way to build SQL statements with maximum readability and security, plus minimum chance of errors.

By building your queries using strings and concatenating the arguments in, you open the door to SQL Injection attacks. By manipulating the contents sent, it is feasible to insert and/or view data that otherwise would not be available to the user. I am still pretty new to C#, but what jmcilhinney posted looks to be the equivalent of the Java PreparedStatement. Assuming MS implemented it well, it should do all escaping for you which clears up the possibility of SQL Injections.

While using replace functions can alleviate SQL Injection possibilities, it means that EVERY query you write must go through the same routines. To me, this sounds like a lot of extra, unneccessary work. In addition, if you forget to escape even one query, you have potentially opened your database to the world.