Have Geek Brain

How do I store a string containing quotes in a MySQL database?

I have the following table:

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

I am using the following SQL statement to store values ​​in a table

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

Now I am trying to store the next line as "text".

Another #haiku: Suffering in the head; the feeling of sleep will not be cured; I need coffee.

and I am getting the following error:

Error: you have an error in your SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use near treatment; I need coffee. ')' On line 1

I think this must be a fairly common problem. How to solve it?

+2


a source to share


5 answers


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



+4


a source


Note that merging a SQL query like this carries potential security risks.

The best way to do this is to use prepared statements . This basically means that some landlords are put in your SQL where the actual values ​​(like yours $sender_screen_name

and $text

) will be displayed .

I searched for "web" and came up with the following piece of code showing how prepared statements can be executed in PHP (sorry if this might not be the best PHP, I usually don't program anything in that language) - but I think it might serve 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 have to worry about how you might escape your lines. PHP and RDBMS will handle this for you automatically.

+4


a source


you can use mysql_real_escape_string

+3


a source


You are avoiding single quotes \'

. See manual .

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


a source


You should never create SQL by concatenating a string with user input. This opens your application for SQL injection (just imagine what happens when someone logs in '); delete from received_queries;

like $text

).

Therefore, you should always use a database structure (like DB for PHP) that allows you to pass in a string with placeholders and then an array with values. The framework will then make sure that the values ​​don't harm.

+2


a source






More articles:


All Articles