[Gambas-user] Wiki Observation, SQL syntax

[Benoît Minisini]

Le 02/01/2015 08:22, Lewis Balentine a écrit :
> For some reason I do not seem to be able to log into the Gambas Wiki.
> That is probably a good thing considering my level of ignorance (which
> has proven to be immense). Thus I pass these observations along for
> consideration:
>
> I have dealt with various SQL Data Bases for some two decades (primarily
> Oracle but also MS SQL, Access, etc.). When I started looking at some of
> the Gambas functions I found a syntax pattern that I was unfamiliar with.
>
> Ref: http://gambaswiki.org/wiki/comp/gb.db/_connection/find
>
>
> Function *Find* ( _Table_ As String *[* , _Request_ As String,
> _Arguments_ As , ... *]* ) As Result
> <http://gambaswiki.org/wiki/comp/gb.db/result>
>
>
>    *
>
>      _Table_
>
>    *
>
>      _Request_
>
>    *
>
>      _Arguments_
>
>
> I assume that this form comes from a language that I am unfamiliar with
> (or perhaps an implementation of stored procedures) and thus goggled:
> SQL Request Arguments
> I found a few references to MS Excel but no suitable explanation. I
> finally found the answer in the "obsolete" Gambas Wiki that is still online:
>
> Ref: http://gambasdoc.org/help/comp/gb.db/connection/subst?v3
> Creates a SQL sentence by substituting its arguments into a format string.
>
>    * _Format_is the SQL sentence.
>    * _Arguments_are the arguments to substitute.
>
> The&1,&2... patterns inside the_Format_string are replaced by the SQL
> representation of the 1st, 2nd..._Arguments_.
>
> These arguments are quoted according to the underlying database SQL syntax.
>
>
> I also note from an example in the "obsolete" Gambas Wiki that an
> explanation mark "!" is used to delimit field names in a result.
> http://gambasdoc.org/help/comp/gb.db/connection/edit?v3
>
> I do not find any similar example in the current Gambas Wiki but perhaps
> I missed it.
>
> Regards,
>
> Lewis
>
>

The documentation of the Connection has to be fixed, because of recent 
changes.

The Find, Edit, Delete, Exec methods of the Connection classes work a 
bit like the Subst$() function : it allows you to separate the SQL query 
from its argument to prevent SQL injection attacks.

Instead of writing:

MyConnection.Exec("SELECT * FROM MyTable WHERE MyField = '" & MyValue & "'")

you have to write:

MyConnection.Exec("SELECT * FROM MyTable WHERE MyField = &1", MyValue)

Gambas will make the substitution for you, and will take care of all the 
necessary quoting that will avoid any SQL injection risk.

Regards,

-- 
Benoît Minisini