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

[Tobias Boege]

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?

-- 
"There's an old saying: Don't change anything... ever!" -- Mr. Monk