PHP + MySQL - Syntax error with quotes in String

Question

PHP + MySQL - Syntax error with quotes in String

Navigation

#1 by (3 votes)
#2 by (-2 votes)

0

"When the string has no single quotation marks, this works fine, my problem is when there are single quotation marks"

example with single quotes:

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

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

With this, a syntax error occurs, because VALUES is actually going like this:

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

As the string #store has a single quotation mark, this conflicts with the other single quotation marks, error occurring:

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 occurs:

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

I have already tried to use backstricks in place of the single quotes in VALUES:

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

But this error occurs in MySQL (I believe in all columns):

Unknown column 'mike' in 'field list'

And that's it ...

In DB, is there any way to store a string that contains single quotation marks?

php mysql sintaxe

asked by anonymous 12.03.2017 / 17:03

2 answers

3

mysqli has a feature that is just 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 .

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

In addition, assuming "Michael" is dynamic, entered by the user , mysqli_real_escape_string does not prevent XSS attacks, so use htmlentities in the output of the text, how much to display it.

12.03.2017 / 18:24

After the validation method is true, how to cancel the sending of the data to the bank? ec2 aws does not respond to the installed application

score -2 · Accepted Answer

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('\', "function escape_mimic($inp) { 
    if(is_array($inp)) 
        return array_map(__METHOD__, $inp); 

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

    return $inp; 
} 

echo escape_mimic("Esse é um tes't feito");
", "\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