5

When I try to instert a string that contains a quote mark, the INSERT query fails. How can I insert strings with single or double quotes?

for example:

$message = "Bob said 'don't forget to call me'";

mysql_query("INSERT INTO someTable (messages) VALUES ('$message')");

I figure the input needs to be filtered but which function should I use for that?

Starx
  • 77,474
  • 47
  • 185
  • 261
Sam
  • 53
  • 1
  • 3
  • I know you accepted the answer from "Your Common Sense", but you really need to go back and look at the answer from @Steven Schlansker because you should start good programming habits from the beginning. – ryandenki Jul 18 '12 at 10:45

3 Answers3

6

See: mysql_real_escape_string

You should ALWAYS be escaping things anything provided by the user before they go it goes into the database to prevent SQL injection in any case.

Billy ONeal
  • 104,103
  • 58
  • 317
  • 552
  • even worst. what if we have that string the OP mentioned, not from user? no escaping then? – Your Common Sense Jun 26 '10 at 02:47
  • @Billy ONeol, I didn't know it can give an unescaped data on its own. How to do that? – Starx Jun 26 '10 at 02:51
  • I mean escaping is not a magic wand. It does not make any data "safe". It works only with quote delimited strings. So, if you are going to add a number, without quotes, mysql_real_escape_string would be useless then. – Your Common Sense Jun 26 '10 at 02:54
  • @Starx: It's automatically unescaped. The escaping is soley for the benefit of the SQL parser -- it does not go into the database escaped unless you use something that was NOT designed for databases ... i.e. `addslashes`. (It's unescaped by the SQL parser before it goes into the database) @Col: True. I assumed in cases like that if it was provided by the user you'd do the conversion in PHP, not the database. – Billy ONeal Jun 26 '10 at 03:03
  • `unless you use something that was NOT designed for databases` - what's the difference? :) – Your Common Sense Jun 26 '10 at 03:21
  • @Billy ONeal, I am sorry to spell your name incorrectly. But what you are saying is not applicable in my cases, I have to personally use stripslashes() to retrieve the original content. Do you know what might be causing this – Starx Jun 26 '10 at 04:52
  • @Starx it's `magic quotes`. You have to disable it (there are plenty of answers already on SO) and then clean all your data already in the database. – Your Common Sense Jun 26 '10 at 05:05
  • @Col. Shrapnel: `addslashes` escapes too much. Some of the characters it escapes do not need to be escaped in SQL, and therefore the SQL parser does not unescape them as it goes into the database. The extra escaped characters would need to be manually unescaped. – Billy ONeal Jun 26 '10 at 12:00
5
$message = mysql_real_escape_string($message);

this function should be applied to every variable you are going to insert into query in quote marks. No exceptions.
It is not really protection from sсaring injection but just a syntax rule.

Though you will need a real protection too. I've explained that in detail in recent answer: In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

Community
  • 1
  • 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
4

A whole hour of a question and nobody has mentioned that escaping strings is evil, and you should use prepared statements instead? Pretty effective way to eliminate SQL injection

Steven Schlansker
  • 37,580
  • 14
  • 81
  • 100