Handle quotes and commands in SQL string

Question

In my MySQL table, I have a column of TEXT type. On my HTML Form, user pastes text into it that might contain "" , ' ( ) and so on. I want to know how to safely execute Insert Query if these characters exist in the text and might crash the query execution.

How to handle them properly in PHP?

Answer 1

Use a prepared statement.

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

A quick example,

$db = new mysqli('localhost', 'username', 'password', 'db');
$stmt = $db->prepare("INSERT INTO mytable (text_column) VALUES (?)");
$stmt->bind_param("s", $mytext); // s = string, b = boolean, i = int, etc
$stmt->execute();
...

Answer 2

If you are not using prepared statements (either with PDO or MySqli) you should pass user's input trough MySql_Real_Escape_String() function. Or MySqli_Real_Escape_String() if you are using MySqli (but not prepared statements).

I would, however, advise you to use prepared statements as your life will be much easier and you get SQL-Injection protection for free.