CODEWITHSUNDEEP
phpmysqlpdolimit
Steve Robbins
Steve Robbins

Reputation: 13812

Force Limit on User MySQL Query

So I have an area where people can write their own query against my database, but I want to limit the returned results (much like PhpMyAdmin).

So I need to check $_POST['query'] to see if has a limit statement, if it does, make sure it's under 30, say. If there is no limit, I need to add it.

How would I do this?

Upvotes: 0

Views: 317

Answers (2)

inhan
inhan

Reputation: 7470

You can use preg_* functions to check the query and inject your preferred limit.

EDIT: I totally ignored possible uses of the LIMIT keyword. Here's the edited version.

$query = 'SELECT * FROM aa LIMIT 100,20';
$limit = 20; // predefined max limit

// possibilities:
// LIMIT N[,N]
// LIMIT N OFFSET N

preg_match('~(?<=\blimit\s)\s*(?<limit>\d+)\s*(,\s*\d+|\soffset\s+\d+)?\s*$~i',$query,$limitStr);
if (isset($limitStr['limit'])) {
    $maxLimit = min((int)$limitStr['limit'],$limit);
    // user might have already limited it so this is
    // just to make sure it does not exceed your max.
    $query = preg_replace('~(?<=\blimit\s)\s*\d+\s*(,\s*\d+|\soffset\s+\d+)?\s*$~i',$maxLimit.'$1',$query);
} else $query .= " LIMIT $limit";

echo $query;

will output SELECT * FROM aa LIMIT 20,20

Upvotes: 1

Mate
Mate

Reputation: 5274

Ignoring risks ... I think the best is to use the query as a subquery and apply limit. So you avoid evaluating the query .

SELECT * 
FROM (
 SELECT * 
 FROM 'table'
 LIMIT 0 , 100
) AS query

LIMIT 0 , 30

Upvotes: 4

Related Questions