Escaping single quote in PHP when inserting into MySQL

Question

I have a perplexing issue that I can t seem to comprehend     I have two SQL statements    The first enters information from a form into the database  The second takes data from the database entered above  sends an email  and then logs the details of the transaction   The problem is that it appears that a single quote is triggering a MySQL error on the second entry only  The first instance works without issue  but the second instance triggers the mysql error     Does the data from a form get handled differently from the data captured in a form   Query 1 - This works without issue  and without escaping the single quote    result   mysql query  INSERT INTO job log  order id  supplier id  category id  service id  qty ordered  customer id  user id  salesperson ref  booking ref  booking name  address  suburb  postcode  state id  region id  email  phone  phone2  mobile  delivery date  stock taken  special instructions  cost price  cost price gst  sell price  sell price gst  ext sell price  retail customer  created  modified  log status id  VALUES    order id     supplier id     category id      value  id         value  qty        customer id     user id     salesperson ref     booking ref     booking name     address     suburb     postcode     state id     region id     email     phone     phone2     mobile   STR TO DATE   delivery date     d  m  Y      stock taken     special instructions     cost price     cost price gst     sell price     sell price gst     ext sell price     retail customer      date  Y-m-d H i s   time           date  Y-m-d H i s   time         1        Query 2 - This fails when entering a name with a single quote  for example  O Brien    query   mysql query  INSERT INTO message log  order id  timestamp  message type  email from  supplier id  primary contact  secondary contact  subject  message content  status  VALUES    order id      date  Y-m-d H i s   time          email     from     row- gt supplier id     row- gt primary email     row- gt secondary email     subject     message content    1

User · Accepted Answer

You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

http://us3.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

Once you store the data, and subsequently retrieve it again, the string you get back from the database will not be automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

User · Answer

mysql real escape string   or str replace   function will help you to solve your problem   http   phptutorial co in php-echo-print

User · Answer

For anyone finding this solution in 2015 and moving forward     The mysql real escape string   function is deprecated as of PHP 5 5 0   See  php net  Warning  This extension is deprecated as of PHP 5 5 0  and will be removed in the future  Instead  the MySQLi or PDO MySQL extension should be used  See also MySQL  choosing an API guide and related FAQ for more information  Alternatives to this function include   mysqli real escape string    PDO  quote

User · Answer

You should do something like this to help you debug   sql    insert into blah values    myVar     echo  sql    You will probably find that the single quote is escaped with a backslash in the working query  This might have been done automatically by PHP via the magic quotes gpc setting  or maybe you did it yourself in some other part of the code  addslashes and stripslashes might be functions to look for    See Magic Quotes

User · Answer

You can do the following which escapes both PHP and MySQL    lt    text     lt a href  javascript window open     http   www google com        gt  lt  a gt      gt     This will reflect MySQL as   lt a href  javascript window open  http   www google com     gt  lt  a gt    How does it work    We know that both PHP and MySQL apostrophes can be escaped with backslash and then apostrophe        Because we are using PHP to insert into MySQL  we need PHP to still write the backslash to MySQL so it too can escape it   So we use the PHP escape character of backslash-backslash together with backslash-apostrophe to achieve this

User · Answer

I had the same problem and I solved it like this    text   str replace             YourContent     There is probably a better way to do this  but it worked for me and it should work for you too

User · Answer

You have a couple of things fighting in your strings    lack of correct MySQL quoting  mysql real escape string    potential automatic  magic quote  -- check your gpc magic quotes setting embedded string variables  which means you have to know how PHP correctly finds variables   It s also possible that the single-quoted value is not present in the parameters to the first query  Your example is a proper name  after all  and only the second query seems to be dealing with names

User · Answer

You should just pass the variable  or data  inside   mysql real escape string trim  val       where  val is the data which is troubling you

[php] Escaping single quote in PHP when inserting into MySQL

Examples related to php

Examples related to mysql

Examples related to insert

Examples related to escaping