1

I discovered an error on my pagination script within CodeIgniter:

 $this->db->where("by_id",$user_id);
 $this->db->order_by("date","desc");
 $this->db->limit(10,$from);
 $query = $this->db->get("status");

The url looks like this : server/demo/page/10

so if user type server/nedjma/baniss/1000000000000000000000

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1000000000000000000000, 10' at line 5

SELECT * FROM (status) WHERE by_id = '58' ORDER BY date desc LIMIT 1000000000000000000000, 10

can you tell me please what's the bug ?

Community
  • 1
  • 1
aniss.bouraba
  • 443
  • 9
  • 18
  • 1
    Sorry mate, just removed the link to that site given it was open to SQL Injection. Someone else will answer in detail (I don't know enough about php) but you need to validate the '$from' variable, to make sure it is a number *only*. – Noon Silk Sep 17 '09 at 02:25
  • @silky, validation of the 'from' variable happens to be a valid answer in all languages, not just PHP. – Vineet Reynolds Sep 17 '09 at 02:28
  • Vineet: Yeah, I just don't know from the top of my head how to confirm it's an int in php; crazy un-typed languages :P – Noon Silk Sep 17 '09 at 02:30
  • 1
    @silky, CodeIgniter's active record ($this->db) automatically checks that $from is a number only. – jimyi Sep 17 '09 at 03:59
  • @jimyi, visiting /page/23/home/1beefcake1 results in ... ORDER BY `cf_pages`.`pages_sort` asc LIMIT 1beefcake1, 3. – sisve Sep 17 '09 at 04:50

1 Answers1

4

It's not a CodeIgniter vulnerability or bug. It's simply an SQL/MySQL issue. I did a little testing with phpMyAdmin, the largest offset you can use is somewhere around 18000000000000000000.

Anything larger, and you will get an SQL syntax error. If you want to prevent this error from happening, just check to make sure $from isn't greater than 18 x 10^18, or create your own custom error pages. You could also just turn error reporting off - at the top of CI's index.php, error_reporting(0);

One final note - the code you posted isn't open to SQL injection. CodeIgniter's Active Record class escapes and checks your input for you. If $from is not a number, then Active Record won't generate a LIMIT clause when creating the SQL.

jimyi
  • 30,733
  • 3
  • 38
  • 34
  • I agree, its a valid number, just too large for mysql, do a check for a maximum and this should be eliminated. – Jakub Sep 17 '09 at 04:31