Thread: Escaping metacharacters

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?

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
whosoeverbelieveth in him should      not perish, but have everlasting life."     John 3:16 
 

>>>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

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