CODEWITHSUNDEEP
phpcodeignitersecuritysql-injection
Martin Pozděna
Martin Pozděna

Reputation: 41

Preventing MS SQL Injections with CodeIgniter

I would like to find an optimal way how to mitigate SQL injection in the web application developed in CodeIgniter framework (web application uses MS SQL backend DB with ODBC connection).

Let's assume I have a simple vulnerable code like this:

$this->db->query("SELECT * FROM users WHERE Login = '".$_GET['name']."'");

This query is clearly vulnerable to SQL injection through HTTP GET parameter "name".

I have read CodeIgniter documentation and everything I could find online to see how to mitigate this simple SQL injection in CodeIgniter and I have tried all of the following options:

Option 1:

$this->db->query("SELECT * FROM users WHERE Login = ".$this->db->escape($_GET['name']));

Option 2:

$this->db->select("*")->from("users")->where('Login', $_GET['name'])->get();

Option 3:

$query = "SELECT * FROM users WHERE Login = '?'"; 
$this->db->query($query, array($_GET['name']));

Option 4:

$query = "SELECT * FROM users WHERE Login = ?"; 
$this->db->query($query, array($_GET['name']));

I was shocked to find out that all four aforementioned options are just as vulnerable to SQL injection as the initial query. I was wondering whether CodeIgniter is so poorly designed from the security perspective or if I am missing some important piece of configuration.

Is there any conceptual way how to prevent SQL injection in CodeIgniter in this case?

Upvotes: 2

Views: 802

Answers (2)

Martin Pozděna
Martin Pozděna

Reputation: 41

OK after several hours of fighting this issue I managed to find an answer myself.

There was SQL injection vulnerability in CodeIgniter ODBC driver prior to version 3.1.0. So even if the coding style (Query Bindings) is correct from security standpoint (as demonstrated in Option 4) the application is still vulnerable to SQL injection through user supplied input.

I verified that by installing both CodeIgniter 3.0.6 and 3.1.0 side by side running the same query. Code in CodeIgniter 3.0.6 was still vulnerable to SQL injection while the one running in CodeIgniter 3.1.0 was not.

CodeIgniter change log https://www.codeigniter.com/userguide3/changelog.html

So lesson learned for the future. Always remember to check framework version as well.

Upvotes: 2

Alex Mac
Alex Mac

Reputation: 2993

  • check every single user input for datatype and where applicabile with regular expressions (never trust user input)
  • use prepared statements

it's a lot of work especially if your application is in bad shape but it's the best way to have a decent security level

the other way (which i'm advising against) could be virtual patching using mod_security or a WAF to filter out injection attempts but first and foremost: try to write robust applications (virtual patching might seem to be a lazy way to fix things but takes actually a lot of work and testing too and should really only be used on top of an already strong application code)

Upvotes: 1

Related Questions