PHP + Mysql - Quote syntax error in String

Question

"Obs: when the string has no simple quotes, it works perfectly, my problem is when it has simple quotes"

example with single quotes:

$name = "Michael";
$store = "Mike's Store";

"INSERT INTO database(name, store) VALUES('$name', '$store')";

with this, a syntax error happens, because the VALUES are actually passing like this:

"INSERT INTO database(name, store) VALUES('Michael', 'Mike's Store')";

like the string #store has a simple quotes, this gives conflict with the other simple quotes, happening error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Store' at line 1

I have tried to use backstricks in the column names and the same error happens:

"INSERT INTO database(`name`, `store`) VALUES('Michael', 'Mike's Store')";

I have tried to use backstricks instead of single quotes, in VALUES:

"INSERT INTO database(name, store) VALUES(`$name`, `$store`)";

but this error happens in Mysql(I believe in all columns):

Unknown column 'mike' in 'field list'

and that’s it...

in DB, there is some way to store a string that contains single quotes?

Answer 1

The mysqli has a resource that is precisely for this purpose, the mysqli_real_escape_string.

$name = mysqli_real_escape_string($conexao, "Michael");
$store =  mysqli_real_escape_string($conexao, "Mike's Store");

_{The $conexão is the mysqli link, started by mysqli_connect.}

---

Only to supplement the use of this feature will hinder ("prevent") SQL Injection attacks, provided that defined a mysqli_set_charset correctly, as it is in the documentation.

Also, assuming "Michael" is dynamic, entered by the user, the mysqli_real_escape_string does not prevent attacks of type XSS, for this use together the htmlentities in the output of the text, when to display it.

Answer 2

To escape ' utilize addslashes() it inserts a bar \ that escapes the value.

example:

$name = "My name is D'Orail";
echo addslashes($name);
//result 
# My name is D\'Orail

Access documentation for further information

This way the content can be saved without problems.

Answer 3

Try to "escape" the characters:

function escape_mimic($inp) { 
    if(is_array($inp)) 
        return array_map(__METHOD__, $inp); 

    if(!empty($inp) && is_string($inp)) { 
        return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp); 
    } 

    return $inp; 
} 

echo escape_mimic("Esse é um tes't feito");

Output:

This is a tes’t done