Codeigniter error with escaping values ​​when passing array with query using "in" in where clause

Question

I have a function below in my model for the codeigniter project, and the $id variable is an array and, for example, contains (1,2,3) . Now, when I revise it, I think that in fact I am not avoiding my $id array. I think I would have to change the line $this->db->escape($id) to $id = $this->db->escape($id)

If I do this, then it puts single quotes around each element in the array and treats it as one long string as follows: '(1,2,3)' .

Can someone confirm that I am not really avoiding my variable or suggesting a solution or let me know if this is an error within codeigniter?

 function get_ratings($id) { $this->db->escape($id); // had to manually escape the variable since it being used in an "in" in the where clause. $sql = "select * from toys t where t.toy_id in ($id)"; $query = $this->db->query($sql, $id); if($query->num_rows() > 0) { return $query->result_array(); } else { return false; } } 

Answer 1

You may be interested in using the CI Active Record class:

In addition to simplicity, the main advantage of using Active Record features is that it allows you to create database-independent applications, because the query syntax is generated by each database adapter. It also allows safe queries, since the values ​​are automatically escaped by the system .

Your rewritten request will look like this (assuming $id is an array):

 $this->db->where_in('toy_id', $id)->get('toys'); 

Also: I admit I'm a bit confused, as it looks like $ids would be a more suitable variable name and the way you use it in a query, I would assume this is a string ...

If the active record is not your thing, you can also find Query Bindings :

The second advantage of using bindings is that the values ​​are automatically escaped , creating more secure queries. You do not need to forget to manually delete data; the engine does this automatically for you.

---

EDIT . Looking back, it looks like what you are trying to do. In this case, try replacing:

 $sql = "select * from toys t where t.toy_id in ($id)"; 

FROM

 $sql = "select * from toys t where t.toy_id in (?)"; 

And pass $id as the second argument to query() , but as a comma-separated string ( implode(',', $id) if $id is really an array).

---

Otherwise, you can use $this->db->escape_str() .

$ this-> db-> escape_str () This function skips the data passed to it, regardless of type.

Here is an excerpt from the mysql driver source code to perhaps calm your mind.

 function escape_str($str, $like = FALSE) { if (is_array($str)) { foreach ($str as $key => $val) { $str[$key] = $this->escape_str($val, $like); } return $str; } // continued... 

It iterates over arrays and avoids their values.

It seems that $this->db->escape will not work for arrays.

$ this-> db-> escape () This function defines the data type so that it can only escape string data.

Here is the source:

 function escape($str) { if (is_string($str)) { $str = "'".$this->escape_str($str)."'"; } elseif (is_bool($str)) { $str = ($str === FALSE) ? 0 : 1; } elseif (is_null($str)) { $str = 'NULL'; } return $str; } 

It looks like it is ignoring arrays.

In any case, I hope you find a solution that works for you. My vote for Active Record.

Answer 2

What you want to do is to avoid individual values ​​in the array. That way you can use array_map in the array first.

 $id = array_map('some_escape_function', $id); 

See: http://php.net/manual/en/function.array-map.php

Then you can do:

 $in = join(",",$id); 

Your SQL will be as follows:

 WHERE t.toy_id in ($in) 

What gives you:

 WHERE t.toy_id in ('1','2','3') 

Answer 3

You can try something like this:

 $sql = 'select * from toys t where t.toy_id in ('. join(',',array_map(function($i) { return $this->db->escape($i); }, $id)).');'; 

* Disclaimer: I cannot access my PHP / MySQL server right now, so I have not confirmed this. Some modification and / or customization may be required.

Answer 4

Here is the solution I use for this with CI 2.1.2:

1) Copy /system/database/DB.php in application / database / DB.php and around line 123 so that it looks like this:

 ... if ( ! isset($active_record) OR $active_record == TRUE) { require_once(BASEPATH.'database/DB_active_rec.php'); require_once(APPPATH.'database/MY_DB_active_rec' . EXT); if ( ! class_exists('CI_DB')) { eval('class CI_DB extends MY_DB_active_record { }'); } } ... 

2) Create MY_Loader.php in the application / kernel:

 class MY_Loader extends CI_Loader { function __construct() { parent::__construct(); log_message('debug', 'MY Loader Class Initialized'); } public function database($params = '', $return = FALSE, $active_record = NULL) { // Grab the super object $CI = & get_instance(); // Do we even need to load the database class? if (class_exists('CI_DB') AND $return == FALSE AND $active_record == NULL AND isset($CI->db) AND is_object($CI->db)) { return FALSE; } //require_once(BASEPATH . 'database/DB.php'); require_once(APPPATH . 'database/DB' . EXT); if ($return === TRUE) { return DB($params, $active_record); } // Initialize the db variable. Needed to prevent // reference errors with some configurations $CI->db = ''; // Load the DB class $CI->db = & DB($params, $active_record); } } 

3) Create an application / database / MY _DB_active_rec.php:

 class MY_DB_active_record extends CI_DB_active_record { public function __construct($params) { parent::__construct($params); log_message('debug', 'MY Active Record Database Driver Class Initialized'); } private function _array_escape(&$str) { $str = "'" . $this->escape_str($str) . "'"; } function escape($str) { if (is_array($str)) { array_walk($str, array($this, '_array_escape')); return implode(',', $str); } elseif (is_string($str)) { $this->_array_escape($str); } elseif (is_bool($str)) { $str = ($str === FALSE) ? 0 : 1; } elseif (is_null($str)) { $str = 'NULL'; } return $str; } } 

Then you just pass an array of values:

 $in_data = array(1, 2, 3); $this->db->query('SELECT * FROM table WHERE id IN(?)', array($in_data)); 

This is not very, but it seems to be a trick!

Answer 5

To link them, you can do the following:

 $queryParams = []; // to add the appropriate amount of bindings ? $idBindings = str_replace(' ', ',', trim(str_repeat("(?) ", count($ids)))); // add each id with int validation foreach ($ids as $id) { if(is_int(intVal($id)) == TRUE){ array_push($queryParams, intVal($id)); } } // the other option commented out below is to merge without checking - // note: sometimes values look like numeric values but are actually strings //queryParams = array_merge($queryParams, $ids); $sql = "select * from toys t where t.toy_id in ('. $idBindings .')"; $query = $this->db->query($sql, $queryParams ); 

Answer 6

Code Igniter v3 now automatically preempts array values:

http://www.codeigniter.com/userguide3/database/queries.html

Query Bindings

Bindings simplify query syntax, allowing the system to combine queries for you. Consider the following example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?"; $this->db->query($sql, array(3, 'live', 'Rick'));

Question marks in the query are automatically replaced with> values ​​in the array in the second parameter of the query function.

Binding also works with arrays that will be converted to IN sets:

$sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?"; $this->db->query($sql, array(array(3, 6), 'live', 'Rick'));

The final query will look like this:

SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick'

The second advantage of using bindings is that the values ​​are automatically escaped, creating more secure queries. You do not need to remember to manually delete data; the engine does this automatically for you.