Thread

Hi,

I am writing SQL queries in CodeIgniter that use variables from URL.  So, example:

URL/page/id

SELECT * 
FROM table
WHERE id=$id

As a test I put a ’ escape character in my URL and CodeIgniter gives message “The URI you submitted has disallowed characters.”. 

So I am wondering, do I really need to escape these variables?  CodeIgniter seems to be handling this already.  Thank you.

for security reasons codeigniter have disallowed some characters, check the config.php file with this option

$config['permitted_uri_chars']

you can append the character which is required for you

Actually, I prefer if CodeIgniter disallows these character. I am wondering if I need if I still need to escape the variables in the SQL query with $this->db->escape($id) since CodeIgniter is already forbidding harmful characters from passing through the URL.

Thanks.

If you use Active Record, they get escaped going in.

I’m writing straight SQL requires with $query = $this->db->query(’ SELECT ...’).  It seems I don’t need to escape variables since apostrophes can’t pass through URLs. Not sure if there is a way for a hacker to circumvent it.

If you’re not using active record, you should manually escape all input.

Read up on this article.. maybe it helps you with making such decisions in the future.

http://web.securityinnovation.com/appsec-weekly/blog/bid/56172/Assume-All-Web-Application-Input-is-Malicious

I’m writing straight SQL requires with $query = $this->db->query(’ SELECT ...’).  It seems I don’t need to escape variables since apostrophes can’t pass through URLs. Not sure if there is a way for a hacker to circumvent it.

Using straight SQL without escaping input is a really bad idea. Typically when someone tries an SQL injection they will submit special strings through forms, such as a login or search form. Apostrophes can pass through URL’s, when someone submits a form with name=“D’ni” it is encoded as part of the URL, then decoded by CI so putting a ’ in the URL bar doesn’t simulate an injection attempt.

If you don’t sanitize your input, eventually a bot will find you. When it does, your only option will be to take down your site and manually fix every single DB query that is not escaped.

CI makes parametrized queries easy- even if you don’t want to use Active Record or an ORM.

Here’s an example:

$sql = "
 SELECT 
  c.*, 
  s.name AS state_name, 
  cs.name AS country_name 
 FROM 
  clients c 
  LEFT JOIN states s ON c.state_id = s.id 
  LEFT JOIN countries cs ON c.country_id = cs.id 
 WHERE 
  c.id = ?
";

$query = $this->db->query( $sql, array( $client_id ) )->row_array( );
return $query;