PostgreSQL Escape string
lazarus / 16-Databases

----------------------------------------------------------------------
goodname · 2014-06-05 21:18

The recent thread about PostgreSQL and transactions reminded me about
another issue that was solved in my own application with a kludge some
time ago.

There is a search screen where the 'where-clause' can vary a lot and
be very complicated. The ServerFilter is used heavily. ServerFilter
Strings are currently escaped using QuotedStr which works because the
AfterConnect event executes a query to make sure
standard_conforming_strings=ON. This means that backslash escapes are
not recognized by the database connection.

It would be much better if the libpq functions which are designed for
escaping strings could be used. The PQescapeString function is
apparently accessible  by including postgres3 in the uses clause but
this is not a very good way of getting to the function. As of pg9.0
PQescapeLiteral and PQescapeIdentifier are available but not included
in the postgres3 unit.

http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-
ESCAPE-STRING

Think it would be good for sqldb to have a generic escape function
like SQLEscapeStr to call these database specific escapes. Most
databases could just use QuotedStr.

----------------------------------------------------------------------
LacaK · 2014-06-06 06:01

If there is need , we can add PQescapeLiteral and PQescapeIdentifier
into header files ...

SQL standard defines escaping like QuotedStr does. So I strongly
recommend follow SQL standard and setup database to follow SQL ANSI
mode.

----------------------------------------------------------------------
goodname · 2014-06-06 13:38

Setting the database to the SQL standard escape method on each
connection is how I solved the issue originally. Consider this to be a
kludge as it is database specific and results in extra queries being
sent. PostgreSQL follows the standard by default today but it also
provides escape functions so users don't have to worry about the
standard or non-standard escape setting.

The issue is that it is difficult to access these escape functions. I
have tried adding the postgres3 to the uses clause and it results in a
link error. So it is difficult to access the older PQescapeString
which is currently available in the header files.
/usr/bin/ld: warning: link.res contains output sections; did you
forget -T?
/usr/bin/ld: cannot find -lpq

Ultimately in the interest of cross database support I think sqldb
should have a common SQLEscapeStr method.

----------------------------------------------------------------------
LacaK · 2014-06-09 07:52

Quote from: goodname on June 06, 2014, 03:38:30 pmI have tried adding
the postgres3 to the uses clause and it results in a link error.
Try postgres3dyn which is used also by sqlDB

----------------------------------------------------------------------
goodname · 2014-06-09 14:24

Using postgres3dyn worked. The following function seams to work
properly.

Code: [Select]function TCommon.sqlEscape(value: string): string;
var esc: pchar = '';
begin
 PQescapeString(esc,pchar(value),length(value));
 result:=''''+StrPas(esc)+'''';
end;

----------------------------------------------------------------------
marcov · 2014-06-09 14:38

(or install the -dev(el) package of postgresql for the static libs)