[PROPOSAL] DML value format

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

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