Limiting a SQL Select query

Question

I am connecting to an SQL database in my PHP script and am having trouble with the LIMIT command:

$result = mysql_query("
    SELECT *
    FROM product
    WHERE `category` like \"" . $_GET['category'] . "\" 
    LIMIT 0, 16
");

This all works, except that if I only have 10 rows then $result contains rows 0~10 and then 0~6 as well.

I am using a a while loop while($row = mysql_fetch_assoc($result)) to check if there is a result and then run an action. Is there any way of having it limit the select statement to only show rows 0~10?

You should NEVER inject unwashed text from "unknown" sources (read: anything from the GET/POST/COOKIE globals) into a SQL query. Either use [mysql_real_escape_string()](http://php.net/manual/en/function.mysql-real-escape-string.php) on the variable, or use [PDO prepared statements](http://www.php.net/manual/en/pdo.prepared-statements.php). — Jared Farrish, Jul 24 '11 at 00:16
Also, you should avoid using MySQL's non-standard double quote for strings; it does support the proper usage of single quotes, you might as well use standards compliant code if you can. — El Yobo, Jul 24 '11 at 00:18
@Dor: The OP is claiming that MySQL is padding the results to make 16 rows when there's only 10 -- the first six are repeated. I've never seen that behavior, personally... — OMG Ponies, Jul 24 '11 at 00:22
@OMG Ponies - it's possible the LIKE statement is returning unexpected results — direct00, Jul 24 '11 at 00:23
@direct00: That's more plausible than what I understand the OP to believe is happening. — OMG Ponies, Jul 24 '11 at 00:24

score 1 · Accepted Answer · answered Jul 24 '11 at 00:18

1

$result = mysql_query("SELECT * FROM product
          WHERE `category` like '" . mysql_real_escape_string($_GET['category']) . "' LIMIT 0, 10");

is it what are you looking for? It will give you ten rows maximally..

Additionally, please read this article about SQLi

answered Jul 24 '11 at 00:18

genesis

50,477
20
96
125

* including the appropriate reference to [mysql_real_escape_string()](http://php.net/manual/en/function.mysql-real-escape-string.php), which in my opinion should be part of the answer... :) – Jared Farrish Jul 24 '11 at 00:19
1

A better idea would be to use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) (but I digress). – Jul 24 '11 at 00:21
Not exactly... I know about the edit timeout. ;) @rfw - Please see my comment below the question. :) – Jared Farrish Jul 24 '11 at 00:22
@rfw: I do not use any prepared statements and I never saw reason why would I do this – genesis Jul 24 '11 at 00:22
@JaredFarrish: not exactly? what do you think? – genesis Jul 24 '11 at 00:24
http://stackoverflow.com/questions/1742066/why-is-pdo-better-for-escaping-mysql-queries-querystrings-than-mysql-real-escape – Jared Farrish Jul 24 '11 at 00:24
@genesis: It looks prettier (so you don't have to stare at code with concatenations everywhere), and you can more easily see what you're doing. :) – Jul 24 '11 at 00:24
@rfw: I do not like it because my logs has double size as normal :p. Btw how does it know that it should be int/str? – genesis Jul 24 '11 at 00:25
@rfw: Not necessarily, Prepared Statements requires an extra round- trip to the server. – Dor Jul 24 '11 at 00:26
@Dor: It's a lot more useful if you're doing bulk operations with the same query, I guess. – Jul 24 '11 at 00:26
@rfw: so in case you're inserting 20000 lines into table, right? – genesis Jul 24 '11 at 00:28
1

@genesis: Yes, but I prefer using prepared statements over sanitization of data since it's less human error prone. – Jul 24 '11 at 00:29
This is what i was already doing, didnt notice i had inserted the data twice. Thanks to everyone for pointing out the security issues in this, i hadn't even considered that. – Darc Jul 24 '11 at 00:51
"Why you should not be using mysqli::prepare" - http://joshduff.com/270/why-you-should-not-be-using-mysqli-prepare – TehShrike Jul 24 '11 at 07:20

Limiting a SQL Select query

1 Answers1