[Gambas-user] What is wrong with my query syntax?

ML d4t4full at gmail.com
Fri Dec 29 12:07:29 CET 2017


Just a very stupid observation: The query is completed by the contents
of a textbox control (called TBXName) on the form where it's supposed to
be placed.
If the syntax error comes from GAMBAS, this textbox may not exist
(didn't try actually, but a possibility nonetheless).

On the other hand, if the error comes from the DB Engine (PostgreSQL in
this case), then the query may be malformed; maybe a single quote in the
textbox data is screwing the query up.
For example, suppose the textbox has the name "O'Malley". The query
string would become the following:

  “SELECT * FROM Friends WHERE Name = 'O'Malley'”

As you can see, the WHERE clause becomes "WHERE Name = 'O'" and the
"Malley'" excess string post-clause will trigger the syntax error at the
DB Engine level.
You MUST escape all apostrophes at the very basic level or, better, use
StoredProcedures with Parameters. Google up "SQL Injection" for (lotsa)
more info.
Try with "' OR 1 --" in the textbox data to see some unwanted effects
abused by database attackers...

Regards,
zxMarce

*On 29/12/17 07:42, Gianluigi wrote:*
> The question seems to be placed in the correct place [0]
> Even SQL seems correct, maybe a question of apostrophe?
> Dim sName As String
> sName = TBXName.Text
> sName = Replace(sName,"'","''")
> $Query = “SELECT * FROM Friends WHERE Name = '” & sName & “'”
> [0]
> https://lists.gambas-basic.org/pipermail/user/2017-December/thread.html
> Regards
> Gianluigi
>
> *2017-12-29 11:18 GMT+01:00 Johny Provoost **<johny.provoost at skynet.be
> <mailto:johny.provoost at skynet.be>>**:*
>
>     I always use
>     $Query = “SELECT * FROM Friends WHERE Name = ” & "\"" &
>     TBXName.Text & "\""
>     and use formname.TBXNamer.Text but don't know if that's the problem
>     By the way, This maillist is changed to
>     'gambas-user at lists.sourceforge.net
>     <mailto:gambas-user at lists.sourceforge.net>'.  You should use this one.
>     Kind Regards,
>     Johny
>
>     *Op 29-12-17 om 04:43 schreef Doug Hutcheson:*
>
>         Hi. I'm a new user and I have misunderstood something.
>         I am trying to establish a connection to a PostgreSQL database.
>          At
>         http://gambaswiki.org/wiki/howto/database?ht=database-manager
>         <http://gambaswiki.org/wiki/howto/database?ht=database-manager>,
>         point 6, I read the following example of a query:
>                         Dim $Query As String
>                        $Query = “SELECT * FROM Friends WHERE Name = '”
>         & TBXName.Text & “'”
>         However, no matter how I try, I always get a syntax error on
>         the assignment line. Obviously I am missing something - can
>         you help?
>         Kind regards,
>         Doug
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20171229/3e0a200d/attachment-0001.html>


More information about the User mailing list