On 3-1-2018 06:57, Lionel Elie Mamane wrote:
Hi,
Where is ':' a valid character in Firebird SQL? In particular, is it
ever valid outside of "-delimited identifiers and '-delimited strings?
Context: In the LibreOffice integration of Firebird, we would like to
allow users to use named parameters, that is things like ':foo' instead
of '?' (without the single quotes). E.g.
SELECT foo, bar FROM qux WHERE item_year = :year
instead of
SELECT foo, bar FROM qux WHERE item_year = ?
Which means we have to go through the SQL statement passed by the user
and replace such ':foo' parameters by '?' before giving the SQL
statement to Firebird.
I'd like to avoid completely parsing the SQL statement for that,
mainly so that the SQL statement is not modified by the LibreOffice
SQL parser (and in particular quoting keywords it doesn't know as
identifiers). So I'm hoping to simply go through the string, keep
track of single and double quotes, and replace any word (outside of
quotes) that starts with ':' by '?'. This wouldn't work for
e.g. PostgreSQL because of its cast syntax (namely 'data::type'), but
is it OK for Firebird?
The colon is valid syntax in PSQL, that is in the bodies of EXECUTE
BLOCK (but not in the parameter list), in DDL CREATE, ALTER, CREATE OR
ALTER, RECREATE of trigger, stored procedure, function, package, and
package body. You probably want special handling for this, especially
for execute block (you may want to support named parameters in the
parameter list, but you really don't want to change the body as it
breaks the PSQL code!).
Further more, array ranges use colons. Arrays are relatively obscure in
Firebird, and the support in SQL is limited. Luckily they are always
enclosed in square brackets (eg [1:5]). IIRC Firebird only allows square
brackets with array ranges (or array access), so you should be safe to
handle this similar to handling quoted strings.
And, don't forget about Firebird 3's alternative quoted strings:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-altquoting
I would also suggest to add an option to disable this (maybe on a per
query basis, or at least on a database level), so people have a way out
if there happens to be a bug with your parser.
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
