Hi,

To prevent SQL injections, I try to neutralize SQL metacharacters.

ex:

Code:


> function SQLString($s) {
>     $s = str_replace("'", "\\s", $s)'
>     $s = str_replace("\\", "\\\\", $s);
>     return "'" . $s . "'";



And suppose I use this :

> $cat = $GET["category"];
> $query = " SELECT Id, Title, Abstract FROM News " . "Where Category=" . $cat;




If a malicious user tries to input
1' UNION SELECT 1, Usr, Pass FROM Usr

it would just pass as plain text like 1 \' UNION SELECT 1, Usr, Pass 
FROM Usr

[edit]
And if he tried 1\' UNION (...) it would pass 1\\\' UNION (...) to the 
database server.
[/edit]

Is that safe from SQL injection?

Thu, 15 Jul 2004 22:02:32 GMT
DarkSamurai <julio@invlaid.linux.net>
Escaping metacharacters
On Thu, 2004-07-15 at 23:02, DarkSamurai wrote:
> Hi,
> 
> To prevent SQL injections, I try to neutralize SQL metacharacters.
> 
> ex:
> 
> Code:
> 
> 
> > function SQLString($s) {
> >     $s = str_replace("'", "\\s", $s)'
> >     $s = str_replace("\\", "\\\\", $s);
> >     return "'" . $s . "'";

Have you looked at the function PQescapeString() in the libpq library? 
Using that would seem to be a simpler way of solving this problem.

Libraries such as Perl DBI have similar functions built in.

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For God so loved the world, that he gave his only 
      begotten Son, that whosoever believeth in him should 
      not perish, but have everlasting life."     John 3:16 


Sun, 18 Jul 2004 23:09:10 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Escaping metacharacters
>>>function SQLString($s) {
>>>    $s = str_replace("'", "\\s", $s)'
>>>    $s = str_replace("\\", "\\\\", $s);
>>>    return "'" . $s . "'";
> 
> 
> Have you looked at the function PQescapeString() in the libpq library? 
> Using that would seem to be a simpler way of solving this problem.

If he's using PHP, he should be using the pg_escape_string() function.

Chris


Mon, 19 Jul 2004 09:25:45 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Escaping metacharacters

DarkSamurai <julio@invlaid.linux.net> writes:

> And suppose I use this :
> 
> > $cat = $GET["category"];
> > $query = " SELECT Id, Title, Abstract FROM News " . "Where Category=" . $cat;

 From a security point of view you're even better off using something like

$dbh->query("SELECT id, title, abstract FROM news WHERE category = ?", $cat);

Or whatever the equivalent syntax is in your driver. Avoiding mixing
user-provided data with the code entirely. The driver may still have to do the
mixing but it's probably better at it than you are. And in newer versions of
Postgres it doesn't even have to do it at all, and can ship the data to the
server separately.

-- 
greg


18 Jul 2004 22:44:36 -0400
Greg Stark <gsstark@mit.edu>
Re: Escaping metacharacters