CODEWITHSUNDEEP
phpmysqlcodeignitersql-injection
random425
random425

Reputation: 719

Codeigniter SQL Injection

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.

Upvotes: 1

Views: 1073

Answers (4)

mdamia
mdamia

Reputation: 4557

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();

Upvotes: 0

nomistic
nomistic

Reputation: 2962

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

Upvotes: 0

volkinc
volkinc

Reputation: 2128

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

Upvotes: 3

Jim Wright
Jim Wright

Reputation: 6058

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();

Upvotes: 0

Related Questions