Problem storing string containing quotes

Question

I have the following table -

$sql = "CREATE TABLE received_queries
    (
     sender_screen_name varchar(50),
     text varchar(150)
    )";

I use the following SQL statement to store values in the table

$sql = "INSERT INTO received_queries VALUES ('$sender_screen_name', '$text')";

Now I am trying to store the following string as 'text'.

One more #haiku: Cotton wool in mind; feeling like a sleep won't cure; I need some coffee.

and I get the following error message

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't cure; I need some coffee.')' at line 1

I think must be a pretty common problem. How do I solve it?

Answer 1

You escape the single quotes \'. See the manual.

One more #haiku: Cotton wool in mind; feeling like a sleep won\'t cure; I need some coffee.

Answer 2

You can use mysql_real_escape_string

Answer 3

I would suggest using prepared statements (with mysqli or pdo) rather than escaping.

Answer 4

You should never build SQL by concatenating a string with user input. This opens your application to SQL injection attacks (Just imagine what happens when someone enters '); delete from received_queries; as $text).

Therefore, you should always use a DB framework (like DB for PHP) which allows you to pass a string with placeholders and then an array with the values. The framework will then make sure that the values can't do any harm.

Answer 5

Please note that putting together an SQL query like that bears potential security risks.

A better way to do this is by using prepared statements. What that basically means is putting certain placeholders into your SQL where actual values (such as your $sender_screen_name and $text) would go.

I've searched the 'net and came up with the following code snippet, showing how prepared statements can be done in PHP (sorry if this is might not the best PHP, I don't usually program anything in this language -- but I guess it can serve as a reasonable starting point):

$dbSelect = $objDb->prepare("INSERT INTO received_queries VALUES (:ssn, :text");

$dbSelect->bindParam('ssn', $sender_screen_name);
$dbSelect->bindParam('text', $text);

By executing your query as a prepared statement, you don't need to worry about escaping your strings yourself. PHP and the RDBMS will handle that for you automatically.