Re: [gnome-db] (un)escape string

[Vivien Malerba <vmalerba gmail com>]

On 3 December 2010 21:00, Piotr Pokora <piotrek pokora gmail com> wrote:
> Hi!
>
> Sorry if the questions are trivial, but I am bit confused.
>
> If I use prepared statements with variables set via gda_set_set_holder_value
> and given value is a string. Should I explicitly escape such string?

No you should not. This is the main reason why it's better to use variables.

>
> If yes, why it can not be done internally?
>
> If I use GdaSql structures (4.0), to build select query, should I unescape
> strings explicitly too?

If you use the GdaSqlStatement and related structures, and
specifically the GdaSqlExpr structure, then the expr->value is
rendered as it is, ie. if it's a string it should already be in the
correct escaped format.

The reason for this is that the GdaSqlStatement is not meant to be
easy to use, but is rather a direct mapping of the actual SQL (and is
what SQL parsers build). In 4.2 there is the GdaSqlBuilder object
which allows you to build GdaSqlStatement using an API and when you
use it, again you don't have to worry about escaping strings or not.

>
> If yes, why it can not be done internally?
>
> Why MySQL provider misses this functionality?
>
> Not sure about postgres, I found provider_class->unescape_string being set
> in sqlite only.

The string escaping and unescaping feature is a historic hertiage of
previous versions of Libgda where using variables was not the
recommended way of doing things. Since then I've been much worried
about SQL injection problems and decided to fully focus on the
variables usage, and thus kind of "neglected" the escaping and
unescaping features. Correcting this is not on my TODO list because it
encourages people to not use variables, and also because the escaping
and unescaping functions are very had to write correctly and are
mostly bugged (even the ones from MySQL, PostgreSQL, ...).

I'll update the documentation to make all this more clearer.

Regards,

Vivien