Hi, I am just wondering how to have safe parameters avoiding to use
parametrized queries. How to behave quote_literal against sql injection
attempts? Is it adequate or must we use something stronger? Like for
example (Perl):

sub sanitize{
my $p=shift;
$p=~ s/[\\"'\*\#]+//g;
$p=~ s/-+/-/g;
return $p;
}

particularly I am wondering how quote_literal behaves against sql remarks.

have fun:
http://xkcd.com/327

"news" wrote:
quote_literal surrounds the string with ', doubles any ' in the string,
and if the string contains a backslash, that backslash will be doubled
and the string prefixed with E.

The idea is to modify a string so that it can safely be used in dynamic
SQL, like

EXECUTE 'SELECT * FROM ' || quote_literal(user_input)

Your code sample has nothing to do with that, it removes certain
characters from the string and will render it useless.

quote_literal will protect you against SQL injection. Just make
sure you don't forget to use it in your code.

Yours,
Laurenz Albe

Il 30/03/2010 9.14, Laurenz Albe ha scritto:
why useless? It simply removes dangerous chars from user input(remarks
in other sql dialects are included too):

' " # * \
* is removed for /* */ remarks style
any multiple dash like -- or --- is turned in a single dash

in my opinion those chars are arguably eligible as content in a database
text field, so they can be removed without worries. Of course any value
parsed in this way must be surrounded by single quotes and this method
is viable only for text fields.

I made a test ended in a scary way:

root@pluto:~# ./testinjection.pl
test input string:
'); drop table IDoNotExist; --

dynamic query string:
select quote_literal('$test') as result

query issued to Postgres:
select quote_literal(''); drop table IDoNotExist; --') as result

DBD::Pg::st execute failed: ERROR: table "idonotexist" does not exist
DBD::Pg::st fetchall_arrayref failed: no statement executing

On Mon, 29 Mar 2010 18:44:12 +0200, news wrote:

The best way to avoid SQL injection is not to form SQL statements
dynamically, from input. You should use bind variables instead.


--
http://mgogala.freehostia.com

Anselmo Canfora wrote:$5fc30a8 (AT) news (DOT) tiscali.it...
If that is your opinion, and your users have no problem if you
remove the # and * in their input strings, then that's fine.
*I* wouldn't like that (couldn't emphasize any more).

Well, you don't show us what "testinjection.pl" does, but of course
you need to escape the string *before* you send it to the database.
That's pretty obvious, isn't it?

If you want Perl, you can use prepared statements to call
quote_literal without any risk of SQL injection, e.g.:

$sth = $dbh->prepare('SELECT quote_literal(?)');
$sth->execute($test);

Then fetch the query result into $quoted and use it like this:

my $sql = 'DELETE FROM ' . $quoted;
$dbh->do($sql);

and no SQL injection will occur
(All code samples untested).

This is of course a bit 'round the corner, but then quote_literal
is designed more for use in the database (in functions) than in
client programs.

As I said, if you double all ' in the string, double all backslashes,
surround the string with ' and prepend E, you should be safe.

Without having to mutilate the user's input.

Yours,
Laurenz Albe

I wrote:
[an example for quoting in dynamic sql]
That delete statement is nonsense, sorry. I mixed up quote_identifier
and quote_literal. The following is more appropriate:

my $sql = 'DELETE FROM tab WHERE textcol = ' . $quoted;

Yours,
Laurenz Albe

On 2010-03-30, Anselmo Canfora <nobody (AT) nowhere (DOT) net> wrote:

http://search.cpan.org/~turnstep/DBD....1/Pg.pm#quote

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Il 31/03/2010 10.01, Laurenz Albe ha scritto:
This is ok, I was hoping that quote_literal were enough to protect
against sql injection on dynamic generated queries, it would have been
nice
About remark-related characters perhaps there is no need to remove them,
doubling backslashes and single quotes should be sufficient.
I worry about the existence of corner cases that could exploit remark
chars. I am wondering if such cases are viable (without the use of
single quotes).

this is testinjection:

#!/usr/bin/perl -C
use strict;
use warnings;
use lib '/usr/lib/cgi-bin/share';
use PostgreSQL;
use utf8;

my $test=qq|'); drop table IDoNotExist; --|;
print qq|test input string:\n$test\n\n|;
my $p=new PostgreSQL;
$p->{database}='test';
$p->connect;
$p->{sql}=qq|select quote_literal('$test') as result|;
print qq|dynamic query string:\n| . q|select quote_literal('$test') as
result| . qq|\n\n|;
print qq|query issued to Postgres:\n$$p{sql}\n\n|;
$p->execute;

Il 31/03/2010 13.02, Jasen Betts ha scritto:
seems to be very suited for the purpose thanks!

Il 30/03/2010 18.07, Mladen Gogala ha scritto:
I know, but there are cases that require dynamic queries, and,
furthermore, dynamic queries are more handy (and dangerous)