Thread: [PROPOSAL] DML value format

-- English --
Hi,

Is there some way to put values in a INSERT statement
without taking care of apostrophes?

In example:
INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
                                      ^^^^^^^^^^^

I think that it can be used some kind of length-marker
to help the parsing of the value.

In example:
INSERT INTO persons VALUES ('Harry', @11:O'Callaghan);

I think this approach could help reducing the sql
injections.

Regards,
A. Torras.

-- Castellano --
Hola,

¿Hay alguna manera de insertar valores en una
sentencia INSERT sin tener en cuenta apóstrofes?

Por ejemplo:
INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
                                      ^^^^^^^^^^^

Pienso que puede ser usado algún tipo de marcador de
longitud para ayudar el parseo del valor.

Por ejemplo:
INSERT INTO persons VALUES ('Harry', @11:O'Callaghan);

Creo que este enfoque podría ayudar reduciendo las
inyecciones SQL (SQL injections).

Saludos,
Alejandro.




____________________________________________________________________________________
Sé un Mejor Amante del Cine
¿Quieres saber cómo? ¡Deja que otras personas te ayuden!
http://advision.webevents.yahoo.com/reto/entretenimiento.html

On Aug 10, 2007, at 5:56 , Alejandro Torras wrote:

> Is there some way to put values in a INSERT statement
> without taking care of apostrophes?
>
> In example:
> INSERT INTO persons VALUES ('Harry', 'O'Callaghan');

This is pretty much a solved problem: don't interpolate into SQL
statements. Use bind parameters (and prepared statements, if you have
them) instead. For example

sth = dbh.prepare "INSERT INTO persons (given_name, family_name)
VALUES (?, ?)"
sth.execute("Harry", "O'Callaghan")


Michael Glaesemann
grzm seespotcode net

Alejandro Torras escreveu:
> -- English --
> Hi,
>
> Is there some way to put values in a INSERT statement
> without taking care of apostrophes?
>
> In example:
> INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
>                                       ^^^^^^^^^^^
>
> I think that it can be used some kind of length-marker
> to help the parsing of the value.
>
> In example:
> INSERT INTO persons VALUES ('Harry', @11:O'Callaghan);
>
> I think this approach could help reducing the sql
> injections.
>
> Regards,
> A. Torras.
>
> -- Castellano --
> Hola,
>
> ¿Hay alguna manera de insertar valores en una
> sentencia INSERT sin tener en cuenta apóstrofes?
>
> Por ejemplo:
> INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
>                                       ^^^^^^^^^^^
>
> Pienso que puede ser usado algún tipo de marcador de
> longitud para ayudar el parseo del valor.
>
> Por ejemplo:
> INSERT INTO persons VALUES ('Harry', @11:O'Callaghan);
>
> Creo que este enfoque podría ayudar reduciendo las
> inyecciones SQL (SQL injections).
>


Dollar-Quoted String Constants?
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

INSERT INTO persons VALUES ($$Harry$$, $$O'Callaghan$$);

Perhaps use quote_literal() function?
http://www.postgresql.org/docs/8.2/interactive/functions-string.html

Osvaldo

On Aug 13, 2007, at 11:21 , Osvaldo Rosario Kussama wrote:

> Dollar-Quoted String Constants?
> http://www.postgresql.org/docs/8.2/interactive/sql-syntax-
> lexical.html#SQL-SYNTAX-CONSTANTS
>
> INSERT INTO persons VALUES ($$Harry$$, $$O'Callaghan$$);

Do not interpolate values into SQL literals, regardless of which
literal syntax you're using. Use bind parameters.

> Perhaps use quote_literal() function?
> http://www.postgresql.org/docs/8.2/interactive/functions-string.html

If you're writing a database adapter, maybe. Otherwise, use bind
parameters. They're easier and you shouldn't have to worry about
always remembering to use quote_literal.

Michael Glaesemann
grzm seespotcode net

On Aug 10, 6:56 am, atec_p...@yahoo.es (Alejandro Torras) wrote:
> -- English --
> Hi,
>
> Is there some way to put values in a INSERT statement
> without taking care of apostrophes?
>
> In example:
> INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
>                                       ^^^^^^^^^^^
>
> I think that it can be used some kind of length-marker
> to help the parsing of the value.
>
> In example:
> INSERT INTO persons VALUES ('Harry', @11:O'Callaghan);
>
> I think this approach could help reducing the sql
> injections.

If you're worried about sql injections, I'm not sure what kind of
client library you're using to access your pgsql database, but most of
them should have placeholder capability, I think, which definitely
takes care of that.  Your statement would become INSERT INTO persons
VALUES (?, ?) and then you would pass the execution function your two
values and it would escape them properly for you.  At least that's how
it works with Perl and DBI.

Kev