How to insert into a database with special characters?

Question

Here’s what I’m doing INSERT:

$result = mysql_query("INSERT INTO tbl_livro VALUES (NULL, '$titulo', '$autor_livro')");

What happens is that there are authors who have names with special characters, for example: O'Reilly Media, Inc.

What happens is that the ' makes conflict in insertion.

Is there any way to ignore the special characters inside the variable $autor_livro? The goal is to insert with the special characters.

Note: I know I should use mysqli or PDO but in this example it has to be even in mysql.

Answer 1

It is necessary to escape the special characters in your query.

In your case, use mysql_real_escape_string

$titulo = mysql_real_escape_string($titulo);
$autor_livro = mysql_real_escape_string($autor_livro);
$result = mysql_query("INSERT INTO tbl_livro VALUES (NULL, '$titulo', '$autor_livro')");

Except: This is a case worth much more using Mysqli or PDO. Both bear Prepared statements and you would not have this kind of problems when entering parameters in your query.

Example of the use of Prepared statements with PDO:

$stmt = $pdo->prepare("INSERT INTO tbl_livro VALUES (NULL, :titulo, :autor_livro)");
$stmt->bindParam(':titulo', $titulo);
$stmt->bindParam(':autor_livro', $autor_livro);

$stmt->execute();