Storing values with apostrophes in the database

Question

What is the industry standard inserting user input that may contain apostrophes into a database? Such an input will be displayed back to users on a webpage. For example, a user updates some field to "I'm cool". I insert it into my database with this function:

public function updateDatabase($value) {
   $value = mysql_real_escape_string($value);
   Database::instance()->query(
      'UPDATE myTable
       SET myColumn = ' . $value . '
       WHERE foo = "bar"'
   );
}

The database will now store "I\'m cool". To display this value properly and safely back to any user, I would have to clean it with this function:

public function toSafeDisplay($userGeneratedValue) {
   return stripslashes(
      htmlentities(
         $userGeneratedValue
      )   
   );
}

My concern is that doing stripslashes and htmlentities on everything I want to display on a webpage will be very processor intensive. The general concensus on StackOverflow is to not do htmlentities before inserting into the database, so that the data is as raw as possible. This would allow it to be later displayed in any medium, not just websites. So we're forced to do htmlentities at display time. Is this also true with stripslashes? Or is it possible to remove all the slashes before the apostrophes before updating the database without introducing SQL injection attacks?.

Speaking of industry standards, please use bind variables in SQL. — Thilo, Jul 07 '11 at 01:20
I don't know what "bind variables" mean. Google results speak of binding as binding the variable to a certain scope, not of stylistic issues. Can you give an example? — JoJo, Jul 07 '11 at 06:10
Referring to something like this: http://docs.php.net/pdo.prepared-statements — Michael Dean, Jul 07 '11 at 21:00

score 1 · Answer 1 · answered Dec 21 '12 at 00:58

Notice to developers that use mysql_real_escape_string properly:

If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

If you found out that magic_quotes_gpc is set to On turn it Off in php.ini!

Check also: http://gr.php.net/manual/en/function.mysql-real-escape-string.php

score 1 · Answer 2 · answered Jul 07 '11 at 01:20

1

That's not how it works at all. If you escape an apostrophy going into the insert/update when you read it back into php it will NOT be escaped. If you want HTML safe data coming out of the database then make it safe before you put it in.

answered Jul 07 '11 at 01:20

SpliFF

38,186
16
91
120

The slash will not be there, but the apostrophe will. So escaping for HTML is still necessary. – Thilo Jul 07 '11 at 01:22
When I use `mysql_real_escape_string`, the database showed slashes before each apostrophe. Without `mysql_real_escape_string`, the value is stored verbatim, but that's quite dangerous to do. Going with the safe route, I must strip the slashes before displaying it back to the user. – JoJo Jul 07 '11 at 01:38
The same code in Windows PHP & MySQL stored I'm fine as I'm fine but when i transferred the code to Linux Server (CentOS) then it started saving backslashes: I\'m fine Any idea except to stripslashes; – dtakis Dec 20 '12 at 23:00
###UPDATE: If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice. So i found out that magic_quotes_gpc was set to On! Turn it Off – dtakis Dec 21 '12 at 00:56

Michael Dean · Accepted Answer · 2011-07-07T02:27:58.350

0

The database should not store it as I\'m cool, but rather as I'm cool. The escape is to allow the apostrophe to be included as part of the data updated in myColumn. I have seen cases where a site displays I\'m cool back to the user, but that is probably a case of double-escaping.

Edit:

mysql_real_escape_string does not store slashes in the database. It escapes the value in the SQL statement. The only way you would get extra slashes in the database is if you did something equivalent to mysql_real_escape_string(mysql_real_escape_string($value)).

edited Jul 07 '11 at 02:27

answered Jul 07 '11 at 01:21

Michael Dean

1,506
9
11

How do you propose I remove all the slashes that `mysql_real_escape_string` inserts in front of all the apostrophes without introducing an injection attack? – JoJo Jul 07 '11 at 01:40
Added clarification to the answer. I'm not saying you don't do it - I'm saying that the database does not store the backslash as part of the data and therefore should not need to worry about removing the backslash when displaying data. If it is, then you likely have a double-escape problem. – Michael Dean Jul 07 '11 at 02:30
Something is wrong with my `mysql_real_escape_string`. When I do this `$input = "O'Brien"; $output = mysql_real_escape_string($input); echo $input . ' : ' . $output;`, I get this: **O'Brien : O\'Brien**. Any idea what could cause this? – JoJo Jul 07 '11 at 20:27
It is working correctly. Remember that this is used purely in the execution of a SQL statement and does not affect how the data is stored. At insert time, not using it would produce `INSERT INTO TABLE VALUES ('I'm cool')`, which someone could come along and input `');DROP TABLE SOME_USER_TABLE;--` and do something unexpected. You want it to do the right thing and execute the statement as `INSERT INTO TABLE VALUES ('I\'m cool')` and will store `I'm cool` in the database. – Michael Dean Jul 07 '11 at 20:55
You should be able to easily check this by running your code and running a query on your table to inspect the actual stored value. – Michael Dean Jul 07 '11 at 20:55
It turns out that I was calling `mysql_real_escape_string` twice. My lack of understanding that HTML does *not* literally print out \, while the Linux error console *does* literally print out \ had me confused for a while. – JoJo Jul 11 '11 at 02:29

Storing values with apostrophes in the database

3 Answers3

Linked