CODEWITHSUNDEEP
phpmysqlvalidationget
Andrew
Andrew

Reputation: 2851

Validating variables in PHP before running MySQL?

I know this question has been asked a million times, but what is the standard today (2013) for validating a query string before running a variable in an SQL statement?

For example, if I receive a variable "id" from the url via $_GET['id']:

$id = $_GET['id'];

...what should I do before running: 

SELECT * FROM users WHERE id="$id"

is this enough:

$id = mysql_real_escape_string($_GET['id']);

... for this specific example?

Upvotes: 0

Views: 329

Answers (3)

Vyktor
Vyktor

Reputation: 20997

You probably should switch to mysqli from deprecated mysql_ where you can take advantage of mysql::prepare() which allows you to use placeholders:

$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?");
$stmt->bind_param("s", $city);
$stmt->execute();

Which are as safe as SQL can get.

But mysql_real_escape_string() is enough to prevent SQL injection (as long as you enclose string in quotes).

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562290

The simplest validation in this case is just to coerce to integer.

<?php

$id = (int) $_GET['id'];

$sql = "SELECT * FROM users WHERE id=$id";

Then you don't need to put quotes around the variable in your SQL expression. In fact, there are cases where using quotes around a numeric literal messes up the SQL optimizer, so I am always puzzled why so many people get into this habit.

If you want to get into more sophisticated validation, read up on PHP's filter extension.

And finally, an even better (easier, faster, more secure) way to use variables in SQL is to use query parameters. However, PHP's deprecated mysql_* functions don't support query parameters, so you should learn mysqli or PDO.

Upvotes: 1

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

It's irrelevant, since id in the most of the conventional cases is an integer. You have to use:

$id = (int)$_GET['id'];

The standard way nowadays is using parametized queries/prepared statements which are represented in the libs of MySQLi and PDO.

For any manual validation, you need to consider wisely what you expect to recieve to this variable.

As in this case, you only expect integers, nothing else. You don't need people to send through ID -> 1 UNION SELECT ... which is still a valid string, and mysql_real_escape_string won't do anything to it.

Upvotes: 1

Related Questions