How to escape single quotes in MySQL

Question

How do I insert a value in MySQL that consist of single or double quotes  i e  This is Ashok s Pen    The single quote will create problems  There might be other escape characters    How do you insert the data properly

User · Answer

var   mysqli real escape string  conn    POST  varfield

User · Answer

If you are using PHP  just use the addslashes   function   PHP Manual addslashes

User · Answer

If you want to keep     apostrophe in the database use this below code   new value   str replace  quot   quot   quot    quot    value      new value can store in database

User · Answer

For programmatic access  you can use placeholders to automatically escape unsafe characters for you    In Perl DBI  for example  you can use   my  string    This is Ashok s pen    dbh- gt do  insert into my table my string  values     undef   string

User · Answer

Put quite simply   SELECT  This is Ashok  s Pen     So inside the string  replace each single quote with two of them   Or   SELECT  This is Ashok  s Pen    Escape it

User · Answer

Use either addslahes   or mysql real escape string

User · Answer

Use this code    lt  php      var    This is Ashok s Pen         mysql real escape string  var     gt    This will solve your problem  because the database can t detect the special characters of a string

User · Answer

If you use prepared statements  the driver will handle any escaping  For example  Java    Connection conn   DriverManager getConnection driverUrl   conn setAutoCommit false   PreparedStatement prepped   conn prepareStatement  INSERT INTO tbl fileinfo  VALUES       String line   null  while   line   br readLine       null        prepped setString 1  line       prepped executeQuery      conn commit    conn close

User · Answer

The way I do  by using Delphi   TheString to  escape    TheString   bla bla bla  em some more apo S  em and so on      Solution   StringReplace TheString   39      39   rfReplaceAll  rfIgnoreCase      Result   TheString   bla bla bla   em some more apo S   em and so on      This function will replace all Char 39  with      allowing you to insert or update text fields in MySQL without any problem   Similar functions are found in all programming languages

User · Answer

In PHP  use mysqli real escape string   Example from the PHP Manual    lt  php  link   mysqli connect  localhost    my user    my password    world        check connection    if  mysqli connect errno          printf  Connect failed   s n   mysqli connect error         exit       mysqli query  link   CREATE TEMPORARY TABLE myCity LIKE City      city     s Hertogenbosch       this query will fail  cause we didn t escape  city    if   mysqli query  link   INSERT into myCity  Name  VALUES    city            printf  Error   s n   mysqli sqlstate  link        city   mysqli real escape string  link   city       this query with escaped  city will work    if  mysqli query  link   INSERT into myCity  Name  VALUES    city            printf   d Row inserted  n   mysqli affected rows  link       mysqli close  link     gt

User · Answer

There is another way to do this which may or may not be safer  depending upon your perspective  It requires MySQL 5 6 or later because of the use of a specific string function  FROM BASE64  Let s say you have this message you d like to insert    Ah   Nearly Headless Nick waved an elegant hand   a matter of no importance        It s not as though I really wanted to join        Thought I d apply  but apparently I  don t fulfill requirements  -   That quote has a bunch of single- and double-quotes and would be a real pain to insert into MySQL  If you are inserting that from a program  it s easy to escape the quotes  etc  But  if you have to put that into a SQL script  you ll have to edit the text  to escape the quotes  which could be error prone or sensitive to word-wrapping  etc  Instead  you can Base64-encode the text  so you have a  quot clean quot  string  SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K  Some notes about Base64-encoding   Base64-encoding is a binary encoding  so you d better make sure that you get your character set correct when you do the encoding  because MySQL is going to decode the Base64-encoded string into bytes and then interpret those  Be sure base64 and MySQL agree on what the character encoding is  I recommend UTF-8   I ve wrapped the string to 50 columns for readability on Stack  Overflow  You can wrap it to any number of columns you want  or not wrap at all  and it will still work   Now  to load this into MySQL  INSERT INTO my table  text  VALUES  FROM BASE64   SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K       This will insert without any complaints  and you didn t have to manually-escape any text inside the string

User · Answer

You should escape the special characters using the   character   This is Ashok s Pen    Becomes   This is Ashok  s Pen

User · Answer

See my answer to  How to escape characters in MySQL   Whatever library you are using to talk to MySQL will have an escaping function built in  e g  in PHP you could use mysqli real escape string or PDO  quote

User · Answer

You can use this code    lt  php       var    This is Ashok s Pen         addslashes  var     gt    if mysqli real escape string   does not work

User · Answer

Maybe you could take a look at function QUOTE in the MySQL manual

User · Answer

is the escape character  So your string should be      This is Ashok  s Pen   If you are using some front-end code  you need to do a string replace before sending the data to the stored procedure   For example  in C  you can do   value   value Replace               and then pass value to the stored procedure

[mysql] How to escape single quotes in MySQL

Examples related to mysql

Examples related to string

Examples related to quotes