Lazarus guide to SQL escaping
lazarus / 16-Databases

----------------------------------------------------------------------
vfclists · 2012-11-01 04:07

Is there a guide to sanitizing SQL inputs for Lazarus/FPC?
I got bitten my first sql escaping error (it was long overdue) when I
tried to execute the PostgreSQL query below with a value containing an
apostrophe eg. `O'Brien`, using FreePascal and Lazarus

    SQL.Add(format('select * from zones where upper(zn_name) >=  %s
and upper(zn_name) < %s order by zn_name',[sQuote(zoneMin),
sQuote(zoneMax)]));

In the query above SQuote is a function that wraps a string in single
quotes. Is there some standard library for sanitizing SQL query
parameters for  Lazarus/FreePascal or Delphi for that matter?

----------------------------------------------------------------------
taazz · 2012-11-01 04:57

the function QuotedStr will do that for you.

----------------------------------------------------------------------
BigChimp · 2012-11-01 09:55

And, my usual hobby horse, you can use parametrized queries as well:
http://wiki.lazarus.freepascal.org/SqlDBHowto#How_to_use_parameters_in
_a_query.3F

... may lead to some performance wins as well in repetitive execution,
and also deals with e.g. date formatting.

----------------------------------------------------------------------
goodname · 2012-11-01 16:37

The function QuotedStr will escape the quotes if
standard_conforming_strings=on. If it is off then a string that
contains \' will not be escaped properly by QuotedStr.
http://www.postgresql.org/docs/9.2/static/runtime-config-
compatible.html#GUC-STANDARD-CONFORMING-STRINGS

Edit:
PostgreSQL can use dollar quoting as well.
http://www.postgresql.org/docs/current/static/sql-syntax-
lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

Edit2:
Notice that your using upper() in the where clause. Assume that is for
case insensitive search. Read that using lower() is better for some
unicode characters. An index can be created on lower(field) for
performance if it makes sense for your program.