PHP Add quote to array from fetch array

Question

I get error : "Unknown column 'Array' in 'where clause'" perharps from variable $query in my code.

This is my code :

$zzz = mysql_query("SELECT alias FROM table WHERE ColumnA = 'yes'");
while($aaa = mysql_fetch_array($zzz)){
    $array[] = $aaa['alias'];
}
$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN ($array) ORDER BY Column1 DESC, Column2 DESC");

I want to make a SELECT query WHERE 'alias' in table2 not equal to any data in $array which come from fetch array $aaa.

I got a clue to make an array from fetch array from : Array in SQL Query?

But, i don't know how to add 'quote' for each data in array that made from $aaa.

Could anyone tell me how to do this? :)

score 2 · Accepted Answer · edited Mar 02 '13 at 09:32

2

Why not use nested queries? Example:

$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN (SELECT alias FROM table WHERE ColumnA = 'yes') ORDER BY Column1 DESC, Column2 DESC");

As noted in my below comment, however, your interaction appears to be vulnerable to injection attacks. This can be avoided to some degree, as others have stated, but as I have also stated, one of the better ways is to use PDO. Example:

try {
    $dbh = new PDO("mysql:host=localhost;dbname=dbname", "user", "password");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $dbh->prepare("SELECT * FROM table2 WHERE alias NOT IN (SELECT alias FROM table WHERE ColumnA = :bool) ORDER BY Column1 DESC, Column2 DESC");
    $stmt->bindValue(":bool","yes");
    $stmt->execute();
} catch (\PDOException $e) {
    // Something went wrong
}

while ($row = $stmt->fetch()) {
    // do stuff with query
}

PDO ships with php 5.1.

edited Mar 02 '13 at 09:32

Wouter J

41,455
15
107
112

answered Mar 02 '13 at 09:04

Daedalus

7,586
5
36
61

1

However, as a note, your current method of db interaction appears to be vulnerable to injection attacks. I would suggest you look up PDO. – Daedalus Mar 02 '13 at 09:06
DO NOT wrap PDO code into try..catch. it's just useless and wrong – Your Common Sense Mar 02 '13 at 09:21
@YourCommonSense Could you explain? Even the php site recommends this for error catching.. – Daedalus Mar 02 '13 at 09:22
@YourCommonSense I've removed the offending code, could you please remove your downvote? – Daedalus Mar 02 '13 at 09:26
@WouterJ I removed the try-catch as apparently 'it's wrong'. I had it in there before, and I got downvoted for it. – Daedalus Mar 02 '13 at 09:33
@Daedalus you can revert my change, but I don't think it's wrong? You set the `ERRMODE_EXCEPTION` and that means it throws errors, you should catch them with a try...catch block, adding a -1 because of that is just wrong. – Wouter J Mar 02 '13 at 09:35
@WouterJ I don't think it is either, but I'm stumped why `Your Common Sense` thinks so. – Daedalus Mar 02 '13 at 09:36
@Daedalus it is surely not wrong, it is just perfect good code. I suggest you leave it like this, otherwise you teach the people wrong this... – Wouter J Mar 02 '13 at 09:37
@WouterJ I'll leave it like this then.. though I'm still wondering at that other comment. – Daedalus Mar 02 '13 at 09:41
@WouterJ One question for you though.. that backslash on purpose? – Daedalus Mar 02 '13 at 09:43
@Daedalus that's because I normally work with namespaces and the `PdoException` class is in the global namespace, so it needs to be prefixed with a `\\`. – Wouter J Mar 02 '13 at 09:45
Wow, I think you are right! Thankyou! :) And, what do you mean by 'vulnerable to injection attacks'? I'm newbie, could you explain this to me? Thx. – Azhar Mar 02 '13 at 10:18
@Daedalus Wow, and really, i learn a lot after read your answer :) Thankyou Sir! – Azhar Mar 03 '13 at 07:50

mgibsonbr · Answer 2 · 2013-03-02T09:07:56.907

You're trying to use $array directly, and it does not print itself the way you need to. Following the advice in the linked question, you could use implode:

$newarray = implode(", ", $array);
$query = mysql_query("SELECT * FROM table2 WHERE alias NOT IN ($newarray) ORDER BY Column1 DESC, Column2 DESC");

As for adding quotes, you can just concatenate them together. However, I'd also escape the values before quoting, to avoid SQL injection vulnerabilities:

while($aaa = mysql_fetch_array($ambilLarikAkunTerlindungi)){
    $array[] = "'" . mysqli_real_escape_string($aaa['alias']) . "'";
}

PHP Add quote to array from fetch array

2 Answers2