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

[Doug Hutcheson]

On Sun, 2017-12-31 at 20:55 +0100, Tobias Boege wrote:
> On Fri, 29 Dec 2017, ML wrote:
> > 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...
> > 
> 
> Doug, you should listen to this advice about SQL injection. Code
> which
> puts unchecked user input into an SQL query string is easily
> exploited
> and gives the perfect security hole. A user can make the program
> crash,
> bypass filters in the rest of your query hence leak data, delete all
> the tables or add unwanted data -- everything that SQL can do,
> provided
> the executing user has the required privileges.
> 
> Gambas can quote SQL queries for you:
> 
>   hConn.Exec("SELECT * FROM Friends WHERE Name = &1", "O'Malley")
> 
> Better yet, use hConn.Find() if you want to do SELECT queries, like
> so:
> 
>   hConn.Find("Friends", "Name = &1", "O'Malley")
> 
> If you need complex queries and have Gambas >= 3.8, you should use
> the
> SQLRequest class:
> 
>   Dim hSql As New SqlRequest(hConn)
>   Dim sQuery As String
> 
>   sQuery = hSql.Select().From("Friends").Where("Name = &1",
> "O'Malley").And().Where("Age >= &1", 32).OrderBy("Age DESC").Get()
>   hConn.Exec(sQuery)
> 
> SQLRequest knows how to build a proper SQL sentence for the
> underlying
> database driver. Note how your Gambas code, if it uses Find() or
> SQLRequest,
> is free of SQL strings of a specific dialect. You can use the same
> Gambas
> code with MySQL, PostgreSQL, SQLite3, etc. (provided that the drivers
> work
> correctly). This is of course desirable: if you want to run unit
> tests,
> SQLite3 might be a good choice, but you might want to use Postgres
> for
> production and someone else might prefer MySQL.
> 
> Regards,
> Tobi
> 
> PS: when did this become a top-posting mailing list?
> 
Hi Tobi.

Happy 1st of January everyone!

Tobi, as far as top/bottom posting is concerned, I admit I have just
been using my mail system (Evolution) default: wherever it puts the
cursor is where I start typing. If the convention is for bottom
posting, I will try to remember this. Apologies for any inconvenience.

As for your advice, it is all excellent. I had been using the
SQLRequest class and only dropped it to eliminate one possible source
of error. The SQL injection problem is in the forefront of my mind, but
right now I just want to get the bare bones working as simply as
possible.

I will do three things, in order:
1 Create a completely fresh project and type everything in by hand to
ensure there are no cut 'n paste artifacts causing problems.
2 If that does not work, I will create a similar MySql project and see
if it works on my machine.
3 If that works, I will bundle up my PostgreSQL project from step 1 and
a dump of the table I have been using - it has one row and 35 columns
(I know - don't ask - I inherited this mess).

Many thanks to you and everyone who has responded.

Doug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20180101/0889d67c/attachment-0001.html>