CODEWITHSUNDEEP
phpsqlsql-injection
CodTango
CodTango

Reputation: 355

What is the best practice to prevent SQL injection here?

$query = 'SELECT ROW FROM TABLE LIMIT' . $start . ', ' . $limit;

The $start and $limit are user input, so I think it may be injected by inputting something like: 1;CREATE DATABASE A; for $start, which would create a new database 'A'. What will be the best practice to prevent that? Please be specific, including example code will be nice.

Upvotes: 1

Views: 288

Answers (4)

sota
sota

Reputation: 365

Request like you mentioned

$start = 1;CREATE DATABASE A;

won't work, as PHP has disabled stacked queries by default.

$start and $limit are numbers, so just say PHP that those vars are integers:

$query = 'SELECT ROW FROM TABLE LIMIT ' . (int)$start . ', ' . (int)$limit;

This will make SQL Injection impossible, but still hackers can break that request and get some info(eg. traces of error, mysql error), so you just need to validate $start and $limit by regex.

Also, prepared statements are the BEST way to prevent and injections.

Upvotes: 1

Martin
Martin

Reputation: 22770

1) You know start and limit need to be integers so force convert them to be integer types.

2) Use a PDO or MySQLi interface to prevent improper injection of code, DO NOT use native mysql

Upvotes: 0

Neo
Neo

Reputation: 7109

You can use a simple check if it is a numeric value using something like:

if (is_numeric($start) && is_numeric($limit)) {
     $query = 'SELECT ROW FROM TABLE LIMIT' . $start . ', ' . $limit;
}

Upvotes: 0

adeneo
adeneo

Reputation: 318342

Best practice is to use prepared statements with PDO or mysqli, something like

$stmt = $db->prepare("SELECT ROW FROM TABLE LIMIT :limit1, :limit2");

$sth->bindParam(':limit1', $start, PDO::PARAM_INT);
$sth->bindParam(':limit2', $limit, PDO::PARAM_INT);

$stmt->execute();

Upvotes: 1

Related Questions