GNATcoll and parameterized queries have me confused

GNATcoll and parameterized queries have me confused

[Thomas Løcke]

Hey all,

When I insert data into a PostgreSQL database using parameterized
queries, I end up with more characters than I've bargained for.

If I do this:

    Conn.Execute ("INSERT INTO tbl (name) VALUES ('Peter')");

I end up with Peter in the database. Plain Peter. 5 characters.

If I instead do this:

    N : aliased constant String := "Peter";
    P : constant Prepared_Statement := Prepare
      ("INSERT INTO tbl (name) VALUES ($1)");

    Conn.Execute (Stmt => P,
                  Params => (1 => +N'Access));


I end up with 'Peter' in the database. 7 characters. Two single
quotes added by GNATcoll.

I've tried doing a manual parameterized insert using psql:

    PREPARE foo (text) AS INSERT INTO tbl VALUES($1);
    EXECUTE foo ('Peter');

I've also fired up the phpPgAdmin web-tool and tried using that.

And the result is, for both, as expected; plain old 5 character Peter.
So PostgreSQL is apparently not at fault here.

Am I doing something wrong with GNATcoll? I'd rather not have those
extra single quotes added to all my string data.  :o)

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke

Re: GNATcoll and parameterized queries have me confused

[Thomas Løcke]

Just a short blurb about the GNATcoll I'm using: SVN revision 170734

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke

Possible "bug" found in gnatcoll-sql_impl.adb

[Thomas Løcke]

On 2011-02-23 12:58, Thomas L�cke wrote:
> Hey all,
>
> When I insert data into a PostgreSQL database using parameterized
> queries, I end up with more characters than I've bargained for.
>
> If I do this:
>
> Conn.Execute ("INSERT INTO tbl (name) VALUES ('Peter')");
>
> I end up with Peter in the database. Plain Peter. 5 characters.
>
> If I instead do this:
>
> N : aliased constant String := "Peter";
> P : constant Prepared_Statement := Prepare
> ("INSERT INTO tbl (name) VALUES ($1)");
>
> Conn.Execute (Stmt => P,
> Params => (1 => +N'Access));
>
>
> I end up with 'Peter' in the database. 7 characters. Two single
> quotes added by GNATcoll.
>
> I've tried doing a manual parameterized insert using psql:
>
> PREPARE foo (text) AS INSERT INTO tbl VALUES($1);
> EXECUTE foo ('Peter');
>
> I've also fired up the phpPgAdmin web-tool and tried using that.
>
> And the result is, for both, as expected; plain old 5 character Peter.
> So PostgreSQL is apparently not at fault here.
>
> Am I doing something wrong with GNATcoll? I'd rather not have those
> extra single quotes added to all my string data. :o)


The GNATCOLL.SQL_Impl.String_To_SQL function has this in it:

    if Num_Of_Apostrophes = 0
      and then Num_Of_Backslashes = 0
    then
       return "'" & Value & "'";
    end if;

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

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.

But for assignment, using INSERT and UPDATE, it's painfully annoying
that you end up with those two extra single quotes added to all your
string data.

Replacing (in GNATCOLL.SQL_Impl.String_To_SQL)

    return "'" $ Value $ "'";

with

    return Value;

solves the INSERT/UPDATE assignment problem - we then get clean string
data added to the database, without excessive single quotes, but then
all the WHERE criterias obviously fails.

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?

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.

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke

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

[Ludovic Brenta]

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.

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

[Thomas Løcke]

On 2011-02-25 15:44, Ludovic Brenta wrote:
> It seems GNATColl has a bug whereby it incorrectly converts the value
> of bound parameters to SQL, when it should not.


Exactly.

You're much better at expressing this the I am.  :o)


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


That is my intention.

My current setup is PHP/PDO based, and uses prepared and parameterized
queries exclusively. None of my string data have those extra single
quotes, so I'd rather like to have that issue fixed in GNATColl before
I start using it in my environment.

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke

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

[Ludovic Brenta]

On Feb 25, 3:53 pm, Thomas Løcke wrote on comp.lang.ada:
> On 2011-02-25 15:44, Ludovic Brenta wrote:
>
>> It seems GNATColl has a bug whereby it incorrectly converts the value
>> of bound parameters to SQL, when it should not.
>
> Exactly.
>
> You're much better at expressing this the I am.  :o)

There has been no reply from any GNATColl maintainer here on
comp.lang.ada, so I suggest you report the bug to them directly.
There is, unfortunately, no public bug database or a dedicated mailing
list for gnatcoll.  Since gnatcoll is part of the GPS sources, you
should try gps-devel or gps-users  at lists.adacore.com.

--
Ludovic Brenta.

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

[Emmanuel Briot]

This issue is now fixed in svn.

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

[Thomas Løcke]

On 2011-02-28 15:04, Emmanuel Briot wrote:
> This issue is now fixed in svn.


Thank you Emmanuel!

I've just tested it here, and it works perfectly using SVN revision
170922.

-- 
Thomas L�cke

Email: tl at ada-dk.org
Web: http//:ada-dk.org
http://identi.ca/thomaslocke