[Gambas-user] What is wrong with my query syntax?
Doug Hutcheson
owlbrudder at gmail.com
Sun Dec 31 02:26:37 CET 2017
Hi everyone.
I solved that syntax error: it was due to some invisible character(s)
being brought across when I cut and pasted from the wiki. When I typed
the code by hand, the problem went away.
Many thanks for all your help.Doug
On Fri, 2017-12-29 at 14:16 +0100, Gianluigi wrote:
> What an idiot I am, miss the ";" final.
> To avoid mistakes I write the queries like that:
>
> sMySql = "SELECT *"
> sMySql &= " FROM Friends"
> sMySql &= " WHERE Name='" & sName & "'"
> sMySql &= ";"
>
> Regards
> Gianluigi
>
> 2017-12-29 13:03 GMT+01:00 Gianluigi <bagonergi at gmail.com>:
> > AFAIK, it works only with SQLite it does not work with MySQL and
> > Postgres.
> >
> > Regards
> > Gianluigi
> >
> > 2017-12-29 12:35 GMT+01:00 Johny Provoost <johny.provoost at skynet.be
> > >:
> > >
> > >
> > >
> > >
> > > Thats why I always use
> > >
> > >
> > > “SELECT * FROM Friends WHERE Name =" & "\"" &
> > > TBXName.Text & "\""
> > >
> > >
> > > to avoid that. In Belgium we got a lot of names with a ' in
> > > the
> > > name.
> > >
> > >
> > >
> > >
> > > Op 29-12-17 om 12:07 schreef ML:
> > >
> > >
> > >
> > > >
> > > > 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/2
> > > > > 017-December/thread.html
> > > > >
> > > > > Regards
> > > > >
> > > > > Gianluigi
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > 2017-12-29 11:18 GMT+01:00 Johny
> > > > > Provoost <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 'gamb
> > > > > > as-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/data
> > > > > > > base?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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --------------------------------------------------
> > > >
> > > > This is the Gambas Mailing List
> > > > https://lists.gambas-basic.org/listinfo/user
> > > >
> > > > Hosted by https://www.hostsharing.net
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Vriendelijke
> > > Groeten
> > > Johny
> > > Provoost
> > > mailto:
> > > johny.provoost at skynet.be
> > > mailto:
> > > johny.provoost at gmail.com
> > > Website:
> > > http://www.jepe.be
> > > or http://www.johnyprovoost.net
> > >
> > >
> > >
> > >
> > >
> > >
> > > --------------------------------------------------
> > >
> > >
> > >
> > > This is the Gambas Mailing List
> > >
> > > https://lists.gambas-basic.org/listinfo/user
> > >
> > >
> > >
> > > Hosted by https://www.hostsharing.net
> > >
> > >
>
> --------------------------------------------------
>
> This is the Gambas Mailing List
> https://lists.gambas-basic.org/listinfo/user
>
> Hosted by https://www.hostsharing.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20171231/7ab9b560/attachment.html>
More information about the User
mailing list