Quotes in query

[eluser]ZioN[/eluser]
Hi,

I'm working on a project and I need to use a query to search for a name(can be left blank) and multiple zipcodes.

I need to produce this:

Code:

SELECT * FROM (`TABLE`) WHERE `name` like '%%' and Zipcode ('0000', '0001', '0002')

This way it works. This is the code I use:

Code:

$this->db->select('*');
$this->db->from('TABLE');
$this->db->where_in('Zipcode', $var);

this produces:

Code:

SELECT * FROM (`TABLE`) WHERE `Zipcode` IN ('0000,0001,0002') and `name` LIKE '%%'

If I try to test the codeigniter query by setting the zipcodes in an variable manually like this:

$string = '0000', '0001'
codeigniter is producing \'0000\', \'0001\'

I've been lurking the user guide for answers already but without result.

Thanks!

[eluser]ivantcholakov[/eluser]

Code:

// Tested on CodeIgniter 3.0-dev

$table = 'table';
$name = 'John';
$zip_codes = array('0000', '0001', '0002');

echo $this->db
    ->select()
    ->from($table)
    ->where_in('Zipcode', $zip_codes)
    ->like('name', $name)
    ->get_compiled_select();

/*

Result query text:

SELECT *
FROM `table`
WHERE `Zipcode` IN('0000', '0001', '0002')
AND  `name` LIKE '%John%' ESCAPE '!'

*/

[eluser]ivantcholakov[/eluser]

Code:

// Tested on CodeIgniter 2.1.4

$table = 'table';
$name = 'John';
$zip_codes = array('0000', '0001', '0002');

// This would simply fail, because I have no such a table,
// the error message shows the resulting SQL (which I need to see).
$this->db
    ->select()
    ->from($table)
    ->where_in('Zipcode', $zip_codes)
    ->like('name', $name)
    ->get();


/*

Result query text:

SELECT *
FROM (`table`)
WHERE `Zipcode` IN ('0000', '0001', '0002')
AND  `name`  LIKE '%John%'

*/

Both tests give correct results. What version of CodeIgniter do you use?

[eluser]ZioN[/eluser]
I'm running version 2.1.3.

I'm creating a string from an array like this:

Code:

$var = implode("', '", $Zipcodes);

This results in this query:

Code:

SELECT * FROM (`TABLE`) WHERE `Zipcode` IN ('0001\', \'0002\', \'0003')

Why does codeigniter escape it?

[eluser]ivantcholakov[/eluser]
Have a look at system/database/DB_active_rec.php:

Code:

/**
  * Where_in
  *
  * Generates a WHERE field IN ('item', 'item') SQL query joined with
  * AND if appropriate
  *
  * @param string The field to search
  * @param array The values searched on
  * @return object
  */
public function where_in($key = NULL, $values = NULL)
{
  return $this->_where_in($key, $values);
}

You should pass an array (of strings in your case). Just comply with the definition of the method where_in(). The method is to construct this fragment of the query, safely, with proper escaping. By your implode(...) manipulation you are trying to replace its internal functionality, which would not work.

[eluser]Tpojka[/eluser]
What would happen if you leave array as is and use it in your query?

Code:

SELECT * FROM (`TABLE`) WHERE `Zipcode` IN ('$Zipcodes');

[eluser]ivantcholakov[/eluser]
This will work. Then it would be your responsibility to make proper escaping against SQL injections of the each element of the array, before applying implode() function. But why? The query builder does it for you automatically.

[eluser]ivantcholakov[/eluser]
Here is an idea how to convert your input string into an array. Then you can pass this array to the query builder.

Code:

$var_array = explode(',', str_replace(array("'", ' '), '', $var));

[eluser]Tpojka[/eluser]
Can you tell how it looks like your input of zip codes: is that something visitor/user type by self minded?

[eluser]ivantcholakov[/eluser]
This is what you know, I can't tell. Where does the list of zip-codes come from? From the database, from a text file, from a PHP hard-coded array/string? Who makes/updates this list? And in time this situation may change.

Nevertheless, in all cases I would prefer the query builder, if it is able to do the job. It is a safer approach. And it has additional benefits: cleaner code, abstraction from the database server type.