Re: Possible "bug" found in gnatcoll-sql_impl.adb

[Ludovic Brenta <ludovic@ludovic-brenta.org>]

Thomas Løcke wrote on comp.lang.ada:
> The GNATCOLL.SQL_Impl.String_To_SQL function has this in it:

As the name of the function suggests, this converts a string to an SQL
statement (also a string).

Your problem happens not in SQL statements but in bound parameters.
For a bound parameter, it is incorrect to call String_To_SQL because
the value of the bound parameter does not need to be in SQL syntax.

> This is where the extra single quotes are added, and for SQL WHERE
> string criterias this is a good thing.

Only if you do not use bound parameters in your WHERE clauses.

> It simply means that you can do
>
>     Billy : constant String := "Billy";
>     Q : constant SQL_Query := SQL_Select
>      (Fields   => Tmp.Id & Tmp.Name,
>       From     => Tmp,
>       Where    => Tmp.Name = Billy);
>
> and not have to worry about adding the single quotes manually to your
> string.

Right but this produces the legal SQL statement:

SELECT id, name FROM tmp WHERE name = 'Billy'

as opposed to the prepared statement:

SELECT id, name FROM tmp WHERE name = ?

In a prepared statement, you can bind the value Billy (without any
quotes) to the parameter.  BTW, this also applies to the LIKE
operator, in which the value of the parameter can contain % signs that
are wildcards for "zero or more characters".

> Perhaps there's a hidden function somewhere in GNATColl that alleviates
> this annoying issue, but if that is the case, then I haven't been able
> to find it.
>
> Am I really the only one experiencing this? Or do GNATColl users in
> general not care about those extra single quotes added to their string
> data?

It seems GNATColl has a bug whereby it incorrectly converts the value
of bound parameters to SQL, when it should not.

> I'd very much like to help fix this, but GNATColl is a rather large and
> intimidating beast, so I'd rather not dig in too deep if it turns out
> I've just overlooked some brilliant piece of design that fixes all this.

Use prepared statements and bound parameters.  Always.  This avoids
nasty issues such as quoting, protection against SQL injection
attacks, etc.

--
Ludovic Brenta.