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->supplier_id', '$row->primary_email' ,'$row->secondary_email', '$subject', '$message_content', '1')");

awgy · Accepted Answer · 2010-04-22T03:11:53.800

159

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().

edited Apr 22 '10 at 03:11

answered Apr 22 '10 at 02:32

awgy

16,596
4
25
18

1

Yeah ok, thanks for the lesson in proper coding however, this does not answer the question why the two queries to not throw the same error... – sjw Apr 22 '10 at 03:01
1

I'm not looking for the answer on how to fix it - I know how to fix it. I'm looking for the "why"! – sjw Apr 22 '10 at 03:02
1

It's not a matter of *proper* coding, it's a matter of a gaping and dangerous security hole. That being said, I'll add a snippet to my original answer to illustrate the underlying issue. I would do it here but the formatting would be all wonky. :) – awgy Apr 22 '10 at 03:08
@hairdresser, the why is there. It's because you are using magic quotes. – Marcus Adams Apr 22 '10 at 03:16
Thanks for the update awgy - the light is now fully illuminated... Now I understand it... – sjw Apr 22 '10 at 05:41
On another note, can you tell me the "why" behind your comment "magic_quotes_gpc is a bad idea" - I'm a hobby coder so have no formal training and simply toy with it building personal websites... I don't even know if it is switched on and until you commented, I didn't even know it existed or what it did! – sjw Apr 22 '10 at 05:44
Not too worry - I've found information relating to it use and the practice of using it... – sjw Apr 22 '10 at 05:46
My reasoning on avoiding `magic_quotes_gpc` is basically that it's a false sense of security. You cannot change the setting at runtime, so you're beholden to it being enabled in the config (which, in some installations, such as some hosting providers, will be out of your control). It leads to mistakes when you forget that other, non-GPC data must be escaped (which was the issue for this question). And the feature itself is deprecated in PHP 5.3 and will be removed in 6.0. There are lots of other opinions about it, but those are my main thoughts. Glad to hear the answers helped. Cheers! – awgy Apr 22 '10 at 06:25
3

[Function *mysql_real_escape_string()* was deprecated in PHP 5.5.0 and was removed in PHP 7.0.0](https://www.php.net/manual/en/function.mysql-real-escape-string.php). – Peter Mortensen Jul 15 '19 at 14:54
any alternative for that function ? – user7082181 Jul 17 '20 at 08:29
@user7082181 Try `mysqli_real_escape_string()`. More details here: https://www.php.net/manual/en/mysqli.real-escape-string.php . The other alternative is `PDO::quote()`. More info here: https://www.php.net/manual/en/pdo.quote.php Be sure to read the notes and comments as well. – FiddlingAway Nov 30 '22 at 14:48

score 71 · Answer 2 · answered Mar 12 '15 at 12:36

71

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()

answered Mar 12 '15 at 12:36

Amy McCrobie

1,019
7
17

2

mysqli_real_escape_string requires 2 parameters it says – coolcool1994 Jun 12 '15 at 03:35
@coolcool1994 - I had this problem, too, until I found this SO question -- http://stackoverflow.com/questions/25636975/warning-mysqli-real-escape-string-expects-exactly-2-parameters-1-given-wh . It would seem that mysqli_**() functions require the first parameter to be your mysqli database connection variable (I usually name mine something like "$conn"). – RoboBear Mar 21 '16 at 23:47
@coolcool1994 - yes, you need your connection string as well as the thing you're escaping. For a lot of users this will be `$con` or `$conn`. – Brian Powell Feb 23 '17 at 17:31
1

[And function *mysql_real_escape_string()* was removed in PHP 7.0.0](https://www.php.net/manual/en/function.mysql-real-escape-string.php). – Peter Mortensen Jul 15 '19 at 15:06

goat · Answer 3 · 2019-09-12T20:17:01.447

23

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

edited Sep 12 '19 at 20:17

answered Apr 22 '10 at 03:10

goat

31,486
7
73
96

*[Magic Quotes](http://php.net/manual/en/security.magicquotes.php)* now says *"This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0."* – Peter Mortensen Jul 15 '19 at 14:57
**BEWARE!** This answer has *always* been problematic and nowdays you cannot even enable magic quotes anymore because even the PHP language developers finally agreed that it was a stupid idea. All injection attacks are caused by improper encoding and *all encoding depends on context*. The user input that needs to refer to e.g. column name *must* use different encoding from the user input that's supposed to be part of SQL string value. The magic quotes stuff assumed that one context is okay for everything which is obviously an unsafe assumption. I think this answer should be removed. – Mikko Rantalainen Jun 28 '21 at 10:54
@MikkoRantalainen No, this answer is not "problematic". I told him the backslashes escaping the single quotes that he is likely to find after debugging possibly comes from magic_quotes_gpc. I did not recommend enabling it. btw, I think you're confusing encoding with escaping. Escaping is context specific - i.e. escape for an sql context, or an html tag context, or an html attribute context etc... although I suppose an escaping scheme is technically an encoding, but its not how the terms are colloquially used in my experience. When people say encoding, they generally mean like utf8. – goat Jan 05 '22 at 08:02
No, encoding string to be used in middle of SQL string is actually encoding the string as data in SQL syntax. It's true that many people talk about "escaping" but what actually happens is simply encoding. It's no different from encoding data as e.g. UTF-8 instead of UTF-16LE. And *you must understand the context* to correctly encode the data. The magic quotes had no idea of the context you would be using so it was never ever possible to work correctly. They just did half-assed job for encoding data as MySQL string but even that was only partially done. – Mikko Rantalainen Jan 06 '22 at 13:09
You sound like a robot who keeps ignoring convenient details so that you can be "correct". Nobody recommended magic_quotes. His question was why one query works, and why the other fails on a single quote. My answer guided him to find his answer, and made a great prediction as to the cause of the different behavior. Telling someone "I think you might be using magic_quotes" is not a recommendation to start using it. And you're preaching to the choir on encoding/escaping. – goat Jan 08 '22 at 19:54

score 19 · Answer 4 · answered Apr 22 '10 at 03:18

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.

score 18 · Answer 5 · edited Sep 29 '14 at 09:52

You can do the following which escapes both PHP and MySQL.

<?
$text = '<a href="javascript:window.open(\\\'http://www.google.com\\\');"></a>';
?>

This will reflect MySQL as

<a href="javascript:window.open('http://www.google.com');"></a>

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.

\\\'

score 15 · Answer 6 · edited Jul 15 '19 at 15:02

15

You should just pass the variable (or data) inside "mysql_real_escape_string(trim($val))"

where $val is the data which is troubling you.

edited Jul 15 '19 at 15:02

Peter Mortensen

30,738
21
105
131

answered Feb 04 '14 at 21:20

user3272729

181
1
4

score 10 · Answer 7 · edited Jul 15 '19 at 14:58

10

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.

edited Jul 15 '19 at 14:58

Peter Mortensen

30,738
21
105
131

answered Jul 26 '13 at 11:59

user2521037

181
1
2

4

There is a better way... any other one than this. Seriously, don't do this if you don't want your database to get Bobby Table'd. Use PDO (the right way) -- it takes care of escaping for you. Use the deprecated `mysql_real_escape_string` even (not the right way)! Or `addslashes` (not the right way). Anything other than this. – Chris Baker Aug 13 '13 at 14:31
You should do a native function for this purpose. – Pedro Góes Nov 05 '16 at 17:48
Bobby Table'd: [xkcd 327](https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom) – Peter Mortensen Jul 15 '19 at 15:01
This shouldn't be downvoted as it's not 'wrong' – pokeybit Oct 16 '19 at 09:27

score 2 · Answer 8 · answered Jan 31 '14 at 01:45

2

mysql_real_escape_string() or str_replace() function will help you to solve your problem.

http://phptutorial.co.in/php-echo-print/

answered Jan 31 '14 at 01:45

user3255636

39
1

[Function *mysql_real_escape_string()* was deprecated in PHP 5.5.0 and was removed in PHP 7.0.0](https://www.php.net/manual/en/function.mysql-real-escape-string.php). – Peter Mortensen Jul 15 '19 at 15:01

Escaping single quote in PHP when inserting into MySQL

8 Answers8

Linked

Related