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

[Gianluigi]

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/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/3f7d9bc6/attachment-0001.html>