Problem with php query because of quotation marks

Asked

Viewed 945 times

2

I’m having trouble with a php query as follows, Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

This query works normally, but when I need to use a word that has ' in the middle causes a problem in the query.

Example:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','D'Agua');

This query already happens an error because of the word D'Agua. What is the simplest way to solve this? Note: The query is running in PHP.

php mysql query
  • 1

    It is recommended to use Prepared statements Slip quotes with slash is a temporary way.

3 answers

5


The ideal, as already mentioned by @rray, is to use Prepared statements.

But if you’re gonna use string "", there is already a certain function for this. For example, using functions mysqli_:

$sql='INSERT INTO Tbl(campo) VALUES ("'.mysqli_real_escape_string( $conn,"D'Agua").'");';
//ou
$sql='INSERT INTO Tbl(campo) VALUES ("'.$mysqli->real_escape_string("D'Agua").'");';


Or in PDO:

$sql = 'INSERT INTO Tbl(campo) VALUES ('. $conn->quote("D'Agua").');';

(note that the quote already adds quotes in the string)


Obsolete, but if still using functions mysql in "old" applications (do not recommend):

$sql = 'INSERT INTO Tbl(campo) VALUES ("'.mysql_escape_string("D'Agua").'");';


See an example with Prepared statements serving mysqli_:

$stmt = $mysqli->prepare('INSERT INTO TBL(campo) VALUES (?)');
$stmt->bind_param('s', "D'Agua" );
$stmt->execute();

(can be done in PDO, but the PDO by default only simulates Prepared statements, losing precisely its greatest advantages. The mysqli already does natively)

-3

A practical way, but not so pretty:

$string = "d'agua"
$resultado = str_replace("'","\'",$string);

  • I don’t know how to do to get backslash here kkkk has not solved my answer for this reason

  • 2

    This is not a practical way, it is a security breach. If you have this in some code, it is the case to fix it. The way you did, just the person by another backslash in the string that has already undone the "solution". Example: string\'abc will turn string\\'abc, your bar will be undone by the previous bar, and the quote problem remains. The language libraries used by the author of the question already have an appropriate function to deal with this.

-3

use the addslashes function().

Example:

    $query=addslashes("INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
    VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','D'Agua')");
    mysql_query($query);

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.