How to properly convert an array to sql string

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, Aug 18 '17 at 15:13
@SolaimanKmail it might, but not where SQL query parameters are concerned. — ADyson, Aug 18 '17 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, Aug 18 '17 at 15:13
@ADyson yes, it's better to save them in separate table, but faster when the data is not concerned in where queries, so it depends on the business logic — skmail, Aug 18 '17 at 15:23
@ADyson: you mean it's vulnerable if input comes form a user/form, correct? In my specific case I'm doing all the thing alone :) — Luca Reghellin, Aug 18 '17 at 15:28
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, Aug 18 '17 at 15:33

apokryfos · Accepted Answer · 2017-08-18T15:28:36.833

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 '17 at 15:28

answered Aug 18 '17 at 15:14

apokryfos

38,771
9
70
114

2

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. – GolezTrol Aug 18 '17 at 15:18
Thank you. And how about passing/adding things like 'ASC' string to an order statement? I've got quite the same issue. – Luca Reghellin Aug 18 '17 at 15:39
@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. – apokryfos Aug 18 '17 at 15:44

How to properly convert an array to sql string

1 Answers1