From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Prepare/Execute silently discards prohibited ORDER BY values |
Date: | 2015-05-12 00:06:04 |
Message-ID: | 555143EC.1000808@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Tested On: 9.4.1, 9.3.6
Severity: minor
Summary: PREPARE/EXECUTE appears to silently discard ORDER BY parameters.
josh=# \d test
Table "public.test"
Column | Type | Modifiers
--------+------+-----------
test | text |
josh=# insert into test values ('test1'),('test9'),('test3'),('test2');
INSERT 0 4
josh=# prepare foo as select * from test order by $1;
PREPARE
josh=# execute foo('test');
test
-------
test1
test9
test3
test2
(4 rows)
josh=# explain analyze
josh-# execute foo('test');
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..23.10 rows=1310 width=32) (actual
time=0.007..0.007 rows=4 loops=1)
Execution time: 0.026 ms
(2 rows)
What appears to be happening is that the prohibited parameter for ORDER
BY is being silently discarded during EXECUTE. At first I thought it
might just be doing ORDER BY 'test' in the background, but that's not it:
josh=# select * from test order by 'test';
ERROR: non-integer constant in ORDER BY
LINE 1: select * from test order by 'test';
josh=# execute foo(1);
test
-------
test1
test9
test3
test2
(4 rows)
josh=# select * from pg_prepared_statements ;
name | statement |
prepare_time | parameter_types | from_sql
------+------------------------------------------------+-------------------------------+-----------------+----------
foo | prepare foo as select * from test order by $1; | 2015-05-11
16:52:55.369479-07 | {text} | t
(1 row)
So something else is happening here. What should probably be happening
is that PREPARE should throw an error if it gets a parameter in the
ORDER BY clause.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-05-12 00:18:10 | Re: Prepare/Execute silently discards prohibited ORDER BY values |
Previous Message | v-brody | 2015-05-11 22:46:30 | BUG #13273: A query that returns wrongly labeled result instead of syntax error |