Multiple array results in a variable

Question

I've some PHP code:

$categories = mysql_query("SELECT category_id, category_name AS categories FROM categories");

while ($row = mysql_fetch_assoc($categories)){
        $columnvalues[] = $row['categories'];
}

$catlist = $columnvalues[0];

$catchq = mysql_query("SELECT message AS message FROM box WHERE LOWER(message) LIKE '".$catlist."'");
$catchr = mysql_fetch_array($catchq);
$catchx = $catchr['message'];
echo $catchx."\n";

The above code works as intended, but only for the [0] match and if the message contains the name of the category for [0]. What I am trying to accomplish is to have the second query look for any of the four categories found in the first query.

How would I match it against [1], [2], [3] as well? I tried an OR operator, but it doesn't work as I think it would (eg. $catlist = $columnvalues[0] or $columnvalues[1];)

Try with regexp: dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp — maximkou
– maximkou, Commented May 14, 2013 at 19:25
@savafa why? i mean, i know why, but just saying that without explaining why doesn't do much good. besides, it doesn't really matter here—he's not putting any user-supplied data into the queries, so there's no risk of injection. — user428517
– user428517, Commented May 14, 2013 at 19:26
@sgroves, it's really not all because of injection risk, the MySQL is deprecated and is not supported anymore and will be removed sooner or later. — SAVAFA
– SAVAFA, Commented May 14, 2013 at 19:35
should have said that in the first place :P btw mysqli kind of sucks too; there's no reason not to just use pdo unless the performance difference matters in your application (in which case ... why would you be using php?) — user428517
– user428517, Commented May 14, 2013 at 19:37

Explosion Pills · Accepted Answer · 2013-05-14 19:23:34Z

3

You could just loop over $columnvalues:

$query = "SELECT message AS message FROM box WHERE LOWER(message)"
foreach ($columnvalues as $key => $value) {
    if ($key) {
        $query .= " OR ";
    }
    $query .= " LIKE '%$value%' ";
}
$catchq = mysql_query($query);

Your code is vulnerable to injection. You should use properly parameterized queries with PDO or mysqli.

answered May 14, 2013 at 19:23

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

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

8 Comments

Explosion Pills Over a year ago

@sgroves any query that uses concatenation is vulnerable, so LIKE '%$value%' is a vulnerability

user428517 Over a year ago

really? how would you, as a malicious user, exploit this "vulnerability"? 'select * from ' . 'potatoes' is a query that uses concatenation too. is it vulnerable?

Explosion Pills Over a year ago

@sgroves I meant concatenation of variables; perhaps I should have said interpolation

user428517 Over a year ago

my point still stands. concatenating variables is only a vulnerability if the variables being concatenated might contain user input. that's not the case in the code in question.

Explosion Pills Over a year ago

@sgroves how do you know that the contents of the categories table does not contain user input?

|

SAVAFA · Accepted Answer · 2013-05-14 19:32:27Z

First of all use MySQLi, since MySQL is deprecated. This can do what you want to do:

$mysqli = new mysqli($dbhost, $dbuname, $dbpass, $dbname);
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
$categories = $mysqli->query("SELECT `category_id`, `category_name` AS `categories` FROM `categories`");

while ($row = $categories->fetch_array(MYSQLI_BOTH)){
        $columnvalues[] = $row['categories'];
}

$stmt = $mysqli->prepare("SELECT `message` FROM `box` WHERE LOWER(`message`) LIKE ?"); 
foreach($columnvalues as $value){
        $stmt->bind_param("s", $value);
        $stmt->execute();
        $stmt->bind_result($message);
        $stmt->fetch();
        printf("%s: %s<br>", $value, $message);
}
$stmt->close();

Collectives™ on Stack Overflow

Multiple array results in a variable

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related