Do i need to sanitize input if using prepared PHP/MySQL queries?

Question

Given the following piece of code, Do i need to escape and sanitize $city?

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$city = "Amersfoort";

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>

Do you need to sanitize any input when using prepared queries?

score 13 · Accepted Answer · edited May 23 '17 at 12:24

13

No you don't have to escape it or sanitize it for injection protection. For other app specific things you may sanitize it though.

I had a similar question a while back:

mysqli_stmt_bind_param SQL Injection

edited May 23 '17 at 12:24

Community

1
1

answered Jan 20 '11 at 20:12

profitphp

8,104
2
28
21

score 9 · Answer 2 · answered Jan 20 '11 at 20:14

9

@Gary: You copied that fragment of code straight from the manual at php.net about mysqli::prepare. On that same page is the following text:

"The purpose of prepared statements is to not include data in your SQL statements. Including them in your SQL statements is NOT safe. Always use prepared statements. They are cleaner to use (code easier to read) and not prone to SQL injections."

Which is the answer to your question ;)

answered Jan 20 '11 at 20:14

Sander

1,244
1
12
24

6

The trees got in the way of the wood. ;) – Gary Willoughby Jan 20 '11 at 21:37
Yes, but prepared statements often require more resources at both the application layer and data layer - be careful when you see someone say, "always use prepared statements." – Jmoney38 Sep 24 '13 at 11:41
@Jmoney38 Spot on, I think every solution should be thought through carefully instead of just picking it because that it seems to be common practice. – Sander Sep 24 '13 at 15:02

score 1 · Answer 3 · answered Nov 10 '15 at 16:03

I realize this question is very old. I stumbled upon it while searching for the same information myself. I'd comment on Sander's answer but I don't have enough reputation to comment yet.

Sander says you don't need to sanitize, but at http://www.w3schools.com/php/php_mysql_prepared_statements.asp (and I realize w3schools isn't always a completely up to date and accurate source of info, but I find it spells things out in a sensible, easy to understand manner), they say "Note: If we want to insert any data from external sources (like user input), it is very important that the data is sanitized and validated."

So it seems that when using forms and accepting user input, you should sanitize.

score 1 · Answer 4 · answered Jan 20 '11 at 20:23

In addition. In addition.

Prepared statemens, if used correctly, almost completely mitigate SQL injection woes. But you should format/sanitize input data where appropriate nevertheless. Cleaning input is not a security feature, but advisible for robustness and usability. In case of your $city, you might want to strip out all non-word characters:

$city = trim(preg_replace("/[^\w-]+/", " ", $city));

Which again: does not replace database escaping or prepared statements, but ensures coherent data to work with.

But that is not sanitizing (remove any illegal character from the data.), but validation. They are 2 different things. You should always validate your data even with prepared statements. — viery365, Dec 12 '17 at 10:40

Do i need to sanitize input if using prepared PHP/MySQL queries?

4 Answers4