PHP Sanitizing string when reading from database

Question

I'm kind of a bit of a noob when it comes to PHP, so apologies if this is very basic. This question has probably been previously asked, but I am unable to find another post with a similar case to mine. An example of a URL on my site is example.com/read.php?id=1. The read.php file contains the following code:

<?php
    $id = $_GET['id'];
    $sql = mysqli_query($con, "SELECT * FROM pages WHERE id='$id'");
?>

I know that I need to do some kind of sanitation to this, but what would be the best way of doing this in this case? Performance is critical as well, so I don't want to be adding unnecessary code.

Start here => http://stackoverflow.com/q/60174/ and use [**prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO**](http://php.net/pdo) — Funk Forty Niner, Apr 07 '14 at 17:22
If performance is really really really critical, you could cast the value to an integer and even remove the quotes around the variable. But a prepared statement is probably the best solution... — jeroen, Apr 07 '14 at 17:34
If performance is critical, then spend your time profiling your code with a profiler like XDebug once you get the code working and safe. The amount of time spent going through proper parametrized queries will probably not be measurable compared to any other performance bottlenecks you may have. — Andy Lester, Apr 07 '14 at 18:44

score 1 · Answer 1 · answered Apr 07 '14 at 17:53

The easiest solution is to coerce the variable to be an integer. Any non-digits are stripped off, and the value becomes safe to use as a numeric value.

$id = (int) $_GET['id'];
$sql = mysqli_query($con, "SELECT * FROM pages WHERE id=$id");

I agree with other commenters that it's better (faster, more secure) to use prepared queries with parameters. Then it looks like this:

$id = (int) $_GET['id'];
$stmt = mysqli_prepare($con, "SELECT * FROM pages WHERE id=?");
mysqli_stmt_bind_param($stmt, "i", $id);

My sentiment exactly. – Funk Forty Niner Apr 07 '14 at 18:14 — Funk Forty Niner, Apr 07 '14 at 18:14

score 0 · Answer 2 · answered Apr 07 '14 at 17:30

You can use filter_var() function. There are so many types of filters available.

Visit this link: http://www.php.net/manual/en/filter.filters.php

You can get the $id and validate like this:

filter_var($id,FILTER_VALIDATE_INT);

The above function will return the filtered data, or FALSE if the filter fails. Once validates, you can use prepared statements and PDO to get the job done.

For Prepared statements:

http://in2.php.net/manual/en/mysqli.prepare.php

For PDO: http://in2.php.net/manual/en/book.pdo.php

PHP Sanitizing string when reading from database

2 Answers2