How to properly convert an array to sql string [duplicate]

Question

Say I've got this:

$term_query = "AND t.slug IN ('" . $term_slugs . "') ";

where $term_slugs id an array like this:

array('foto', 'video');

If I do a simple implode(), it won't work because it cannot be 'photo, video'. So I should do a foreach cycle to build the "'photo','video'". But I can't think this is the only way... Really unelegant. Is there any other smarter way?

@SolaimanKmail That is about the least appropriate answer you can give. — GolezTrol
– GolezTrol, Commented Aug 18, 2017 at 15:13
@SolaimanKmail it might, but not where SQL query parameters are concerned. — ADyson
– ADyson, Commented Aug 18, 2017 at 15:13
$term_query = "AND t.slug IN ('" . $term_slugs . "') "; this is vulnerable to SQL injection attacks. Parameterised queries and prepared statements should be used. — ADyson
– ADyson, Commented Aug 18, 2017 at 15:13
you must be getting the data from somewhere that could change, otherwise you wouldn't bother using variables for it. As a general principle, it's best never to trust any input, whether it comes directly from a user or not. Plus you should be using parameters as standard practise, it wouldn't even ever enter my head not to do so. Then there's never any doubt. Plus on a more basic level it can help to avoid annoyances such as unexpected SQL syntax errors when you've got awkward input containing things like apostrophes etc. — ADyson
– ADyson, Commented Aug 18, 2017 at 15:33

apokryfos · Accepted Answer · 2017-08-18 15:28:36Z

4

You could use:

$term_query = "AND t.slug IN ('" . implode("','",$term_slugs) . "') ";

However prepared statements are nicer:

$term_query = "AND t.slug IN ('" . implode(",",array_fill(0,count($term_slugs),"?") . ") "

Now you can bind the parameters:

PDO binding

$stmt = $pdoObject->prepare($query); //Query is the full query which contains the parametrised $term_query 
foreach ($term_slugs as $index => &$slug) {
    $stmt->bindParam($index+1,$slug); //+1 because PDO parameters are index starting from 1
}

MySQLi binding

$stmt = mysqli_prepare($query); //Query is the full query which contains the parametrised $term_query 
$refArray = [ $stmt, array_fill(0,$term_slugs),"s") ]; //first two parameters for bind param
foreach ($term_slugs as $index => &$slug) {
    $refArray[] = &$slug; //mysqli_bind_param needs references
} 
call_user_func_array('mysqli_bind_param',$refArray);

edited Aug 18, 2017 at 15:28

answered Aug 18, 2017 at 15:14

apokryfos

40.9k11 gold badges85 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GolezTrol Over a year ago

I think your last sentence is the best solution by far, but a bit more explanation would be very helpful to make sure people will use that over the unsafe and error prone impoded slugs array.

Luca Reghellin Over a year ago

Thank you. And how about passing/adding things like 'ASC' string to an order statement? I've got quite the same issue.

apokryfos Over a year ago

@Stratboy for order by it depends how your table looks like I guess. If you have something like $orderByArray = [ "column asc", "column2 desc" ] then it's easy . the asc or desc can't be parametric AFAIK.

Collectives™ on Stack Overflow

How to properly convert an array to sql string [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related