Codeigniter SQL Injection

Question

I have this code on my controller:

$sql = "SELECT * FROM user WHERE id = " . $this->input->get('foo'); 
$foo = $this->db->query($sql);
echo '<pre>';
print_r($foo->result());
echo '</pre>';
die();

I've noticed that if I use this URL:
www.site.com?foo=1 OR 1 = 1
all data of the user table is shown:

Array
(
[0] => stdClass Object
    (
        [id] => 1
        [email] => [email protected]
        [password] => aaa
    )
[1] => stdClass Object
    (
        [id] => 1
        [email] => [email protected]
        [password] => bbb
    )
[2] => stdClass Object
    (
        [id] => 1
        [email] => [email protected]
        [password] => ccc
    )
)

Is it possible to run another query that returns the data from the user_phone table?

Tables:

CREATE TABLE `user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL
  PRIMARY KEY (`id`),
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


CREATE TABLE `user_phone` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_user` INT(11) UNSIGNED NOT NULL,
  `number` INT(11) UNSIGNED NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Data:

INSERT  INTO `user`(`email`,`password`) VALUES ('[email protected]','aaa');
INSERT  INTO `user`(`email`,`password`) VALUES ('[email protected]','bbb');
INSERT  INTO `user`(`email`,`password`) VALUES ('[email protected]','ccc');

INSERT  INTO `user_phone`(`id_user`,`number`) VALUES ('1','911911911');
INSERT  INTO `user_phone`(`id_user`,`number`) VALUES ('1','922922922');
INSERT  INTO `user_phone`(`id_user`,`number`) VALUES ('2','955955955');
INSERT  INTO `user_phone`(`id_user`,`number`) VALUES ('3','711711711');

Thks

EDIT:
I'm aware of the existing mechanisms to prevent this from happening.
My question is if it's possible, and how, can I get data from other tables.

use real_escape_string or something similar to prevent injection. never trust user input — SuperDJ
– SuperDJ, Commented Aug 21, 2015 at 19:31
Can you use parameter binding instead of directly concatenating values like that? You're essentially executing user input as code. — David
– David, Commented Aug 21, 2015 at 19:33
are you asking for the injection string? in short, yes, it is possible to run a query that will output data from any and all tables/databases that the current mysql user has access to, and more — Alex Andrei
– Alex Andrei, Commented Aug 21, 2015 at 19:34
Hi guys! Thks for the replies. I'm aware of the functions/methods that can prevent this from happening. My question is if it's possible, with this code, return data from other tables. Thks! — random425
– random425, Commented Aug 21, 2015 at 19:43

volkinc · Accepted Answer · 2015-08-21 19:47:47Z

3

I think it's going to be like this.

www.site.com?foo=1 OR 1 = 1 union select * from user_phone where user_phone.id_user = user.id

answered Aug 21, 2015 at 19:47

volkinc

2,1381 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

random425 Over a year ago

Hi volkinc! It fails on the user.id (Unknown column 'user.id' in 'where clause') but I get the picture. Thks a lot!

Jim Wright · Accepted Answer · 2015-08-21 19:38:40Z

0

CI comes with functions to escape variables for exactly this reason.

$foo = $this->input->get('foo');
$foo = $this->db->escape($foo);
$sql = "SELECT * FROM user WHERE id = {$foo}"; 
$foo = $this->db->query($sql);
echo '<pre>';
print_r($foo->result());
echo '</pre>';
die();

answered Aug 21, 2015 at 19:38

Jim Wright

6,0681 gold badge18 silver badges35 bronze badges

1 Comment

random425 Over a year ago

Hi Jim. I'm aware of the escape method. My question is if it's possible with this code return data from other tables.

nomistic · Accepted Answer · 2015-08-21 19:59:45Z

0

You should be able to bind your query using something like this:

$sql = "SELECT * FROM user WHERE id = ? AND name = ?"; 
$foo = $this->db->query($sql, array('foo', 'bar'));

As for getting data from other tables, you'd just need to construct a more elaborate sql query

answered Aug 21, 2015 at 19:59

nomistic

2,9624 gold badges23 silver badges37 bronze badges

Comments

mdamia · Accepted Answer · 2015-08-22 00:23:37Z

0

You asked about querying data from 2 tables, to query 2 related tables you can use join. In your example user and user_phone are related. You perform sql queries with join like demo below. The user primary_key is the glue in user_phone table. 1 - select * 2 - pass the id we want to retrieve 3 - from which table 4 - perform a join or multiple joins 5 - get the result try this

$this -> db -> select('*');
$this -> db -> where('id' => '1');
$this -> db -> from('user');
$this -> db -> join('user_phone', 'user_phone.id_user = user.id');
$query = $this -> db -> get();

edited Aug 22, 2015 at 0:23

answered Aug 21, 2015 at 23:55

mdamia

4,5571 gold badge27 silver badges23 bronze badges

3 Comments

Wai Ha Lee Over a year ago

Can you explain your answer? An answer is much more useful if it explains what is being done so that its techniques can be applied to other situations.

mdamia Over a year ago

right, I was in a bit of a rush, I added info to explain what the statement does and how it is done. Thanks for pointing that out though :).

random425 Over a year ago

Hi mdamia. Thk you for the reply. Basically, my question was how to join tables using SQL Injection, not using code.

Collectives™ on Stack Overflow

Codeigniter SQL Injection

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related