Change PHP indexed Array to Pass in MYSQL SELECT query

Question

Below is My Array

Array ( [0] => test [1] => jaz )

I want to pass this array using IN Function in mysql like that

$q = "select * from claimant where org IN (Array)";

but i need to change my INDEXED ARRAY to simple Array like (1,2,3,4) how i can change array and pass it to mysql IN condition Thanks in Advance

You can use this: `$output = implode(',',array_keys($yourArray));` it's easy and very simple. — aldrin27, Sep 09 '15 at 08:40
With implode you wont be able to escape characters in the array. So he will be exploited to SQL injection. — Jacques Koekemoer, Sep 09 '15 at 08:43

MrD · Accepted Answer · 2015-09-09T09:47:10.530

1

If your array will not contain values like quotes ( " or ') then you can something like this:

$array = array(1=>'test', 2=>'jaz');
$inClause = '"' . implode('","', $array) . '"';
$q = "select * from claimant where org IN ({$inClause})";

Important notes

Even though this code snippet will work for you, if the values in array are comming from user (user makes input in some kind of form or something), then your SQL query will be wurnerable to the SQL Injection attack. That means someone can delete your database, or take your data.

edited Sep 09 '15 at 09:47

answered Sep 09 '15 at 08:43

MrD

2,423
3
33
57

If you use this answer then your values in $yourArray is not escaped. So if someone uses a quote they can break your SQL – Jacques Koekemoer Sep 09 '15 at 08:44
What about your answer then? If someone uses a hack they can kill the database then :) – Hanky Panky Sep 09 '15 at 08:45
That is true, but also there are lot of ifs :) – MrD Sep 09 '15 at 08:46
So what if data is just static data? – MrD Sep 09 '15 at 08:48
When he changes and specifies the question, I will easily change my answer, because now there are just too meni ifs. – MrD Sep 09 '15 at 08:48
@MrD there are allot of ifs yes, but that's the sort of thing that you have to think of when creating a web system / web page – Jacques Koekemoer Sep 16 '15 at 14:11

Jacques Koekemoer · Answer 2 · 2015-09-09T08:42:50.397

0

Convert the array into a string first. You cannot directly use an array in a String.

Use this code to convert the array into a string:

$array = array(0 => 'test',1 =>'jaz');

$str = "";
foreach($array as $value) $str .= ",'$value'";
$str = substr($str,1);

Then you can change your SQL to look like this:

$q = "select * from claimant where org IN ($str)";

That should generate an SQL string like this: select * from claimant where org IN ('test','jaz')

edited Sep 09 '15 at 08:42

answered Sep 09 '15 at 08:36

Jacques Koekemoer

1,378
5
25
50

Yuck, foreach is an ugly solution. implode was designed for this purpose. – Ultimater Sep 09 '15 at 08:50
Yes I agree foreach is not a good solution but how else would you escape the values. This is perfect because now it also gives the developer the chance to manipulate and change the values before processing them – Jacques Koekemoer Sep 09 '15 at 08:53
True, but if we're gonna attempt to do things right for any problematic strings, I'd sooner take advantage of parameter binding: http://stackoverflow.com/questions/14960621/mysql-bind-param-with-in – Ultimater Sep 09 '15 at 09:10

Change PHP indexed Array to Pass in MYSQL SELECT query

2 Answers2

Important notes