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

[Gianluigi]

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/2017-
> December/thread.html
> Regards
> Gianluigi
>
> *2017-12-29 11:18 GMT+01:00 Johny Provoost **<johny.provoost at skynet.be
> <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'.  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,
>>> 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 Listhttps://lists.gambas-basic.org/listinfo/user
>
> Hosted by https://www.hostsharing.net
>
>
> --
>
> ------------------------------
>
> *Vriendelijke Groeten*
>
> *Johny Provoost*
>
> *mailto: **johny.provoost at skynet.be* <johny.provoost at skynet.be>
>
> *mailto: **johny.provoost at gmail.com* <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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20171229/1864f7f6/attachment.html>