[Gambas-user] Eroor when doing SQL

[Benoit Minisini]

On Friday 18 June 2004 14:43, Brant Wells wrote:
> Hey Tim:
>
> It has been my experience with mySql that the & sign causes problems
> when it's by itself.... For example if the title = 'Brant & Tim'  then
> it would fail...   So change the string to 'Brant && Tim'  (note I used
> two &'s)...
>
> But try this first... Instead of using chr(34)...  Try chr(39) (the '
> )...  I know M$ SQL Server is not too big on which one you use, but I
> forget if MySQL is picky about it or not...
>
> HTH,
> ~Brant
>
> Tim Hanschen wrote:
> >I am still trying.... it seems that the exec to the database fails....
> >
> >This is what I do:
> >
> >    rResult = Fhoeren.hDB.Exec("SELECT album from mp3 where title = " &
> > Chr(34) & titel & Chr(34))
> >
> >Is it possible that the Exec interprets the &-sign as a string
> > concatination?
> >
> >regards,
> >  - Tim -
> >

You must be careful, because DB.Exec, DB.Find and DB.Edit work like the 
Subst() function. Every '&x' in the SQL string is replaced by the x-th 
argument passed after the sql request. 

Note that the argument is converted to a sql string depending on its type, AND 
ON THE UNDERLYING DATABASE SYSTEM.

I did that to prevent people from constructing their sql request themselves, 
to avoid sql injection, and to allow them to write database independant sql 
request - which is a performance by itself.

For example, instead of doing:

  rResult = myDB.Exec("SELECT album from mp3 where title = " & Chr(34) & title 
& Chr(34))

you must do:

  rResult = myDB.Exec("SELECT album FROM mp3 WHERE title = &1", title)

This way, your title will be able to include any escaped character: the [&], 
but the ['] too.

Regards,

-- 
Benoit Minisini
mailto:gambas at ...1...