1

I want to do a query similar to the following:

SELECT f_name, l_name, title from
-> employee_data where title 
-> IN ('Web Designer', 'System Administrator');

The search terms are received in an array $data = ['Web Designer', 'System Administrator'] and right now I can turn that array into Web Designer,System Administrator using:

 $data = implode(',', $data)

Is there a good way to turn that array into 'Web Designer', 'System Administrator' so I can insert this phrase into the MySQL query directly as shown at the beginning of the post?

Nyxynyx
  • 61,411
  • 155
  • 482
  • 830
  • 2
    I assume you also want to protect yourself against SQL injection, but just forgot to mention it, right? – Mark Byers Feb 10 '12 at 23:52
  • How do I protect myself against SQL injection? Sorry I'm new to this – Nyxynyx Feb 10 '12 at 23:54
  • @Nyxynyx: Read up on using [PDO](http://php.net/manual/en/book.pdo.php) and [prepared queries](http://www.php.net/manual/en/pdo.prepare.php) in particular. – Leigh Feb 11 '12 at 00:47
  • @Nyxynyx see http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php – John Carter Feb 11 '12 at 00:47

4 Answers4

6

You probably should use:

$data = implode( ', ', array_map( $data, array( $object, 'escape')));
$query .= 'title IN ( ' . $data . ')'; // Append to condition

Where $object->escape() would by function like:

public function $escape( $string){
   return "'" . mysql_real_escape_string( $string, $this->connection) . "'";
}

Here's array_map() documentation, and mysql_real_escape_string(). This should be SQL injection proof solution.

Vyktor
  • 20,559
  • 6
  • 64
  • 96
1

try this

 $data = array('Web Designer', 'System Administrator');
 $query_include = "'".implode("','",$data)."'";
 $query ="SELECT f_name, l_name, title from
          employee_data where title 
           IN ($query_include);";

as Vyktor pointed out that SQL injection can happen and I fully agree so without changing the above code I will sort of prepend this code to above

 function clean(&$item) {
  $item = mysql_real_escape_string($item);
 }
array_walk($data,"clean");
Jaspreet Chahal
  • 2,759
  • 1
  • 15
  • 17
  • How sql injection was born, do something about it :) – Vyktor Feb 11 '12 at 00:02
  • oh yes thats right forgot to handle that. Thanks Vyktor. Editing my answer now. I've an article here which deals with this problem. pretty much in line http://jaspreetchahal.org/php-add-wrap-single-quote-around-all-array-values-separated-by-comma-in-php/ – Jaspreet Chahal Feb 11 '12 at 00:18
  • Yeah, that's better... It's very similar to my approach .) – Vyktor Feb 11 '12 at 00:22
  • yes thats right but your is based on OOP and mine structural :) we both deserve another +! I guess hehe. Do you mind If I add your solution to my blog post?? – Jaspreet Chahal Feb 11 '12 at 00:25
  • here you go :D +1 but use `mysql_real_escape_string()` [http://php.net/manual/en/function.mysql-real-escape-string.php ], `mysql_escape_string()` is deprecated [http://php.net/manual/en/function.mysql-escape-string.php ] – Vyktor Feb 11 '12 at 00:28
  • I +1d u too. I know whats the difference :) just felt lazy i guess hehe – Jaspreet Chahal Feb 11 '12 at 00:28
  • Actually that's why I choose OOP not procedural approach to be able to call `mysql_real_escape_string()` in correct way – Vyktor Feb 11 '12 at 00:29
  • `DEPRECATED` = will be removed in future versions. – Vyktor Feb 11 '12 at 00:29
  • I know man but Very special thanks for sharing. others will benefit from it. I've changed that on all my projects too to be on safe side. – Jaspreet Chahal Feb 11 '12 at 00:32
  • I'm taking these input values via `getJSON` from browser to PHP backend using Codeigniter `$this->input->get('var_name')`. Does Codeigniter cleans them automatically? – Nyxynyx Feb 11 '12 at 01:39
0

Sure you can

$data = "'"
        .implode("','", $data).
        "'";

it is a bit unreadable, but it's working. Also make sure that $data is not user data or if so, make sure it's sanitized.

Martin.
  • 10,494
  • 3
  • 42
  • 68
0

modify the query like this:

$query = 'SELECT f_name, l_name, title from
         -> employee_data where title 
         -> IN (\'' . implode('\', \'', $data) . '\')';
geissler
  • 56
  • 2