Results 1 to 5 of 5

Thread: 's problem when insert into database

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2005
    Location
    Dhaka, Bangladesh
    Posts
    102

    '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
    Please Rate every reply if it is useful to u

  2. #2
    Shared Member
    Join Date
    May 2005
    Location
    Kashmir, India
    Posts
    2,277

    Re: 's problem when insert into database

    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.
    Use [code] source code here[/code] tags when you post source code.

    My Articles

  3. #3
    Hyperactive Member drattansingh's Avatar
    Join Date
    Sep 2005
    Posts
    395

    Re: 's problem when insert into database

    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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,775

    Re: 's problem when insert into database

    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:
    1. mySqlCommand.CommandText = "INSERT INTO Student (ID, Name) VALUES (@ID, @Name)"
    2. mySqlCommand.Parameters.AddWithValue("@ID", id)
    3. mySqlCommand.Parameters.AddWithValue("@Name", name)
    That's the "proper" way to build SQL statements with maximum readability and security, plus minimum chance of errors.

  5. #5
    Addicted Member finn0013's Avatar
    Join Date
    Jan 2001
    Location
    Charleston, SC
    Posts
    222

    Re: 's problem when insert into database

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width