CODEWITHSUNDEEP
phpstringescaping
Lincoln
Lincoln

Reputation: 175

How to deal with apostrophes and double quotes simultaneously in PHP

I have a HTML form, from which a PHP script extracts values, as shown below:

$dbc = mysqli_connect("all required info here...") or die("Error occurred");

$sent = "Any sentence here...which may contain apostrophe or double quotes or both";

$query = "SELECT * FROM myrecord WHERE sentence = '$sent'";
$result = mysqli_query($dbc, $query);
$data = mysqli_fetch_array($result);
mysqli_close($dbc);

The problem is, that the variable $sent can contain any string with a combination of either apostrophe or double quotes or both. This gives an error when going for execution of mysqli_query(). So even if I escape double quotes in initialization of $sent it will still create problem for execution of mysqli_query(). And if I escape both for ' and " then value of $sent does not remains what it actually needs to be (although I am not sure about whether escaping both ' and " will work or not).

Is there any built in function that automatically escapes all special characters of a string? Or any workaround that solves this problem?

[P.S. I have already searched some previous questions on stackoverflow and haven't been able to find a solution.]

Upvotes: 1

Views: 618

Answers (1)

Elias Van Ootegem
Elias Van Ootegem

Reputation: 76395

What you want, and what you should do is used prepared statements (parameterized queries). With PDO, that would look something like this:

$stmt = $pdo->prepare('SELECT * FROM myrecord WHERE sentence = :sentence');
$stmt->execute([':sentence' => $sentence]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    //do stuff
}

mysqli supports them, too, but the API is slightly more cumbersome (IMO) - see mysqli_prepare docs for details:

$stmt = $mysqli->prepare('SELECT * FROM myrecord WHERE sentence = ?');
//or $stmt = mysqli_prepare($connection, $query);

Then, you bind the parameter (the value to be used in the WHERE clause) using bind_param:

$stmt->bind_param('s', $sentence);
//or mysqli_stmt_bind_param($stmt, 's', $sentence);

Then call execute - or mysqli_stmt_execute, and fetch the results using fetch - or mysqli_stmt_fetch...

As mentioned in the comments: the parameters and query string needn't be quoted in any way, because they're treated as separate entities. The result being that you can re-use the same prepared statement with different paramters:

$stmt = $pdo->prepare('SELECT * FROM table WHERE field = :field');
$fieldVals = [123, 46, 32]; // three values:
$results = array_fill_keys($fieldVals, null);
foreach ($fieldVals as $val) {
    $stmt->execute([':field' => $val]);//execute with each value in $fieldVals array
    $results[$val] = $stmt->fetchAll(PDO::FETCH_ASSOC); // fetch results for this field value
    //optional, but good form:
    $stmt->closeCursor();
}

you've now used the same statement 3 times, but only had to send the query string once. The query had to be parsed and processed once, and after that, you merely sent the paramters to the DB. This approach is generally faster, safer (prepared statements protect agains most injection attacks), and just all round better.

Upvotes: 2

Related Questions