PostgreSQL query with concatenation gives "Internal Server Error" only via AJAX (not on pgAdmin)

Question

I have the following query in a PHP code (called via AJAX).

$q = 'select distinct taxon,ord,fam,gen,sp from spp
        join col on gensp = concat(gen,' ',sp)
        where gen ilike $1 or sp ilike $1
        order by taxon,ord,fam,gen,sp';
$res = pg_query_params($conn,$q,["%$gensp%"]);
if ($res) {
    echo "Works!";
} else {
    echo "Error!";
}

$gensp is the value the user is looking for (a species or genus name, or part of it). When I run it on the browser, it gives "Internal Server Error" (unless I erase the join part). In pgAdmin it works, for instance:

select distinct taxon,ord,fam,gen,sp from spp
join col on gensp = concat(gen,' ',sp)
where gen ilike '%Casiornis%' or sp ilike '%Casiornis%'
order by taxon,ord,fam,gen,sp

The above sentence also works in psql. I noticed the problem is with the concatenation function. If I use join col on gensp = sp it works. I have tried different variations:

join col on gensp = cast(gen as text)||' '||cast(sp as text)
join col on gensp = coalesce(gen,'')||' '||coalesce(sp,'')
join col on gensp = gen::text||' '||sp::text
join col on gensp = gen||' '||sp

All produce the same error. What am I doing wrong?

I'm using PostgreSQL 11.7 and PHP 7.3 on Debian 10.

Does this answer your question? [Reference - What does this error mean in PHP?](https://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php) — Dharman, Sep 09 '20 at 19:35

Slava Rozhnev · Accepted Answer · 2020-09-09T17:44:08.863

You need escape quotes here:

$q = 'select distinct taxon,ord,fam,gen,sp from spp
        join col on gensp = concat(gen,\' \',sp)
        where gen ilike $1 or sp ilike $1
        order by taxon,ord,fam,gen,sp';

or use double quotes like :

$q = "select distinct taxon,ord,fam,gen,sp from spp
        join col on gensp = concat(gen,' ',sp)
        where gen ilike \$1 or sp ilike \$1
        order by taxon,ord,fam,gen,sp";

but in this case you need to escape the $ sign for prevent php translation

PostgreSQL query with concatenation gives "Internal Server Error" only via AJAX (not on pgAdmin)

1 Answers1