From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00 autolearn=ham autolearn_force=no version=3.4.4 X-Google-Thread: 103376,65f26d981c0edb76 X-Google-NewGroupId: yes X-Google-Attributes: gida07f3367d7,domainid0,public,usenet X-Google-Language: ENGLISH,ASCII Path: g2news1.google.com!postnews.google.com!v31g2000vbs.googlegroups.com!not-for-mail From: Ludovic Brenta Newsgroups: comp.lang.ada Subject: Re: Possible "bug" found in gnatcoll-sql_impl.adb Date: Fri, 25 Feb 2011 06:44:46 -0800 (PST) Organization: http://groups.google.com Message-ID: References: <4d64f653$0$23761$14726298@news.sunsite.dk> <4d67b8af$0$23765$14726298@news.sunsite.dk> NNTP-Posting-Host: 153.98.68.197 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1298645086 10931 127.0.0.1 (25 Feb 2011 14:44:46 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 25 Feb 2011 14:44:46 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: v31g2000vbs.googlegroups.com; posting-host=153.98.68.197; posting-account=pcLQNgkAAAD9TrXkhkIgiY6-MDtJjIlC User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.0.6) Gecko/2009012111 Red Hat/3.0.6-1.el5 Firefox/3.0.6,gzip(gfe) Xref: g2news1.google.com comp.lang.ada:17594 Date: 2011-02-25T06:44:46-08:00 List-Id: Thomas L=F8cke 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 > > =A0 =A0 Billy : constant String :=3D "Billy"; > =A0 =A0 Q : constant SQL_Query :=3D SQL_Select > =A0 =A0 =A0(Fields =A0 =3D> Tmp.Id & Tmp.Name, > =A0 =A0 =A0 From =A0 =A0 =3D> Tmp, > =A0 =A0 =A0 Where =A0 =A0=3D> Tmp.Name =3D 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 =3D 'Billy' as opposed to the prepared statement: SELECT id, name FROM tmp WHERE name =3D ? 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.