PHP MySQLi query works everwhere but in AGI

Question

I have an asterisk AGI called from dialplan. Everything in the agi script, including other queries, work fine. This select returns 1 row from from CLI and MySQL Workbench but returns 0 rows when run in AGI. I have ensured the EOLs are all unix and checked for other typical AGI oddities but found none. Here's the offending portion of the AGI script:

$mysqli = new mysqli('127.0.0.1', 'mysql_user', 'password');  //these work everywhere
$query="select queuename, agent from asteriskcdrdb.queuelog where event='CONNECT' and callid='$origuid';";
$result = $mysqli->query($query);
$row = $result->fetch_assoc();
$row_cnt = $result->num_rows;
$myagi->verbose("CDR Query row count: $row_cnt");
if (row_cnt == 1) {
    $qnum=$row['queuename'];
    $agent=$row['agent'];
    $myagi->verbose("Agent: $agent Queue: $qnum");
} else {
    $err=$mysqli->error;
    $myagi->verbose("CDR Query ERROR: $err\n$query");
}

This never returns any rows. Yet, if I grab the query printed as out from the asterisk AGI, log into mysql (MariaDB) as same user/pw as AGI from Linux CLI and paste query it returns a row as expected.

What am I doing wrong?

This may be just debugging related: before building the query (which you should use parameters for btw.), you _could_ output on the verbose channel the actual value of the $origuid variable, for example: `$myagi->verbose(sprintf('$origuid: "%s"', $origuid))`. In the script it's an undefined variable and you have not shared what it contains nor where it comes from, but as the script acts differently, it might be that having it on the diagnostic channel could shed more light. — hakre, Aug 31 '23 at 13:37
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Aug 31 '23 at 13:46

score 0 · Answer 1 · answered Aug 31 '23 at 17:05

To get AGI errors on your console do following

# stop asterisk
asterisk -r
core stop now
# start asterisk in THIS console, it will print errors in console
asterisk -vvvvgc
agi set debug on

There is no way say what is wrong with your code, need debug it.

And change this line

$myagi->verbose("CDR Query row count: $row_cnt");

to

$myagi->verbose("CDR Query row count for callid $origuid: $row_cnt");

ps double check that you have index on callid in your db.

PHP MySQLi query works everwhere but in AGI

1 Answers1