Another form of SQL injection

[howachen@gmail.com]

Hi,

In many web articles, people focusing on SQL injection in the form of :


e.g.
/*************** *************** *************** *************/
$name = "tom' UNION blah blah blah"
$query = "SELECT * FROM users WHERE name = '".$name."';
/*************** *************** *************** *************/

However, another form of SQL injection might in the form of...

/*************** *************** *************** *************/
$name = "1 UNION blah blah blah"
$query = "SELECT * FROM users WHERE id = ".$name;
/*************** *************** *************** *************/

for case 1, we can easily solved by escaping the special characters
like " ' ", but how to solve for case 2?

Thanks.

[frizzle]

Re: Another form of SQL injection


howac...@gmail. com wrote:[color=blue]
> Hi,
>
> In many web articles, people focusing on SQL injection in the form of :
>
>
> e.g.
> /*************** *************** *************** *************/
> $name = "tom' UNION blah blah blah"
> $query = "SELECT * FROM users WHERE name = '".$name."';
> /*************** *************** *************** *************/
>
> However, another form of SQL injection might in the form of...
>
> /*************** *************** *************** *************/
> $name = "1 UNION blah blah blah"
> $query = "SELECT * FROM users WHERE id = ".$name;
> /*************** *************** *************** *************/
>
> for case 1, we can easily solved by escaping the special characters
> like " ' ", but how to solve for case 2?
>
> Thanks.[/color]

I believe it would treat 1 UNION blah blah blah as a string, meaning
the query would look like
SELECT * FROM users here id = '1 UNION blah blah blah'

Frizzle.

[howachen@gmail.com]

Re: Another form of SQL injection


frizzle wrote:[color=blue]
> howac...@gmail. com wrote:[color=green]
> > Hi,
> >
> > In many web articles, people focusing on SQL injection in the form of :
> >
> >
> > e.g.
> > /*************** *************** *************** *************/
> > $name = "tom' UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE name = '".$name."';
> > /*************** *************** *************** *************/
> >
> > However, another form of SQL injection might in the form of...
> >
> > /*************** *************** *************** *************/
> > $name = "1 UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE id = ".$name;
> > /*************** *************** *************** *************/
> >
> > for case 1, we can easily solved by escaping the special characters
> > like " ' ", but how to solve for case 2?
> >
> > Thanks.[/color]
>
> I believe it would treat 1 UNION blah blah blah as a string, meaning
> the query would look like
> SELECT * FROM users here id = '1 UNION blah blah blah'
>
> Frizzle.[/color]

However, The resulting query is :

SELECT * FROM users WHERE id = 1 UNION blah blah blah

and it worked!

[Chung Leong]

Re: Another form of SQL injection


howachen@gmail. com wrote:[color=blue]
> Hi,
>
> In many web articles, people focusing on SQL injection in the form of :
>
>
> e.g.
> /*************** *************** *************** *************/
> $name = "tom' UNION blah blah blah"
> $query = "SELECT * FROM users WHERE name = '".$name."';
> /*************** *************** *************** *************/
>
> However, another form of SQL injection might in the form of...
>
> /*************** *************** *************** *************/
> $name = "1 UNION blah blah blah"
> $query = "SELECT * FROM users WHERE id = ".$name;
> /*************** *************** *************** *************/
>
> for case 1, we can easily solved by escaping the special characters
> like " ' ", but how to solve for case 2?
>
> Thanks.[/color]

Yeah, that's something that's often overlooked. What you want to do is
cast the variable to integer. PHP will convert a string that's not a
number into zero, an attempt at SQL injection would yield an no-op.

Also keep an eye out for IN constructs:

$sql = "SELECT * FROM users WHERE id IN (" . implode(',',
$_POST['checkboxes']) .")";

An easy way to prevent injection in this case is to use a intval() on
every element with the help of array_map().

[frizzle]

Re: Another form of SQL injection


Chung Leong wrote:[color=blue]
> howachen@gmail. com wrote:[color=green]
> > Hi,
> >
> > In many web articles, people focusing on SQL injection in the form of :
> >
> >
> > e.g.
> > /*************** *************** *************** *************/
> > $name = "tom' UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE name = '".$name."';
> > /*************** *************** *************** *************/
> >
> > However, another form of SQL injection might in the form of...
> >
> > /*************** *************** *************** *************/
> > $name = "1 UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE id = ".$name;
> > /*************** *************** *************** *************/
> >
> > for case 1, we can easily solved by escaping the special characters
> > like " ' ", but how to solve for case 2?
> >
> > Thanks.[/color]
>
> Yeah, that's something that's often overlooked. What you want to do is
> cast the variable to integer. PHP will convert a string that's not a
> number into zero, an attempt at SQL injection would yield an no-op.
>
> Also keep an eye out for IN constructs:
>
> $sql = "SELECT * FROM users WHERE id IN (" . implode(',',
> $_POST['checkboxes']) .")";
>
> An easy way to prevent injection in this case is to use a intval() on
> every element with the help of array_map().[/color]

but what i forgot is to enclose the variable part of the query (WHERE
clause) within those single quotation marks. That should have it treat
is as a string ...

Frizzle.

[noor.rahman@gmail.com]

Re: Another form of SQL injection

Use PHP 5's mysqli class. It allows query parameters to be binded and
hence forces us to explicitly declare the type of parameter being
passed (string/double/integer/blob). This way, SQL injections that you
mentioned can be prevented.

Hope this helps.


frizzle wrote:[color=blue]
> Chung Leong wrote:[color=green]
> > howachen@gmail. com wrote:[color=darkred]
> > > Hi,
> > >
> > > In many web articles, people focusing on SQL injection in the form of :
> > >
> > >
> > > e.g.
> > > /*************** *************** *************** *************/
> > > $name = "tom' UNION blah blah blah"
> > > $query = "SELECT * FROM users WHERE name = '".$name."';
> > > /*************** *************** *************** *************/
> > >
> > > However, another form of SQL injection might in the form of...
> > >
> > > /*************** *************** *************** *************/
> > > $name = "1 UNION blah blah blah"
> > > $query = "SELECT * FROM users WHERE id = ".$name;
> > > /*************** *************** *************** *************/
> > >
> > > for case 1, we can easily solved by escaping the special characters
> > > like " ' ", but how to solve for case 2?
> > >
> > > Thanks.[/color]
> >
> > Yeah, that's something that's often overlooked. What you want to do is
> > cast the variable to integer. PHP will convert a string that's not a
> > number into zero, an attempt at SQL injection would yield an no-op.
> >
> > Also keep an eye out for IN constructs:
> >
> > $sql = "SELECT * FROM users WHERE id IN (" . implode(',',
> > $_POST['checkboxes']) .")";
> >
> > An easy way to prevent injection in this case is to use a intval() on
> > every element with the help of array_map().[/color]
>
> but what i forgot is to enclose the variable part of the query (WHERE
> clause) within those single quotation marks. That should have it treat
> is as a string ...
>
> Frizzle.[/color]

[Mladen Gogala]

Re: Another form of SQL injection

howachen@gmail. com wrote:[color=blue]
> Hi,
>
> In many web articles, people focusing on SQL injection in the form of :
>
>
> e.g.
> /*************** *************** *************** *************/
> $name = "tom' UNION blah blah blah"
> $query = "SELECT * FROM users WHERE name = '".$name."';
> /*************** *************** *************** *************/
>
> However, another form of SQL injection might in the form of...
>
> /*************** *************** *************** *************/
> $name = "1 UNION blah blah blah"
> $query = "SELECT * FROM users WHERE id = ".$name;
> /*************** *************** *************** *************/
>
> for case 1, we can easily solved by escaping the special characters
> like " ' ", but how to solve for case 2?
>
> Thanks.
>[/color]


This dynamic query forming is the root cause of the problem.
Here is how to solve it:

require_once ('adodb/adodb.inc.php') ;
require_once ('adodb/adodb-exceptions.inc. php');

$db = NewADOConnectio n("oci8");
$SQL="SELECT * FROM users WHERE name = :NAME";
try {
$db->Connect($DSN['database'], $DSN['username'],$DSN['password']);
$db->execute($SQL,a rray("NAME"=>$n ame));
}
catch(Exception $e) {
die($e->getMessage() );
}

The only difference from your situation is the use of "oci8" driver
which is unlikely to work with MySQL. Everything else will work,
including placeholders. That way, you not only eliminate any possibility
for SQL injection but you also help the underlying database as it can
reuse previously parsed cursor. Entering SQL expressions will not have
any effect, as the variable is bound to a placeholder, not used to
create SQL dynamically.
--
Mladen Gogala

http://www.mgogala.com

[Chung Leong]

Re: Another form of SQL injection


frizzle wrote:[color=blue]
> Chung Leong wrote:[color=green]
> > howachen@gmail. com wrote:[color=darkred]
> > > Hi,
> > >
> > > In many web articles, people focusing on SQL injection in the form of :
> > >
> > >
> > > e.g.
> > > /*************** *************** *************** *************/
> > > $name = "tom' UNION blah blah blah"
> > > $query = "SELECT * FROM users WHERE name = '".$name."';
> > > /*************** *************** *************** *************/
> > >
> > > However, another form of SQL injection might in the form of...
> > >
> > > /*************** *************** *************** *************/
> > > $name = "1 UNION blah blah blah"
> > > $query = "SELECT * FROM users WHERE id = ".$name;
> > > /*************** *************** *************** *************/
> > >
> > > for case 1, we can easily solved by escaping the special characters
> > > like " ' ", but how to solve for case 2?
> > >
> > > Thanks.[/color]
> >
> > Yeah, that's something that's often overlooked. What you want to do is
> > cast the variable to integer. PHP will convert a string that's not a
> > number into zero, an attempt at SQL injection would yield an no-op.
> >
> > Also keep an eye out for IN constructs:
> >
> > $sql = "SELECT * FROM users WHERE id IN (" . implode(',',
> > $_POST['checkboxes']) .")";
> >
> > An easy way to prevent injection in this case is to use a intval() on
> > every element with the help of array_map().[/color]
>
> but what i forgot is to enclose the variable part of the query (WHERE
> clause) within those single quotation marks. That should have it treat
> is as a string ...
>
> Frizzle.[/color]

Well, if it's meant to be a string, then it wouldn't have worked in the
first place. If a variable is supposed to be a number, then casting it
to int prevents it from being something else.

[R. Rajesh Jeba Anbiah]

Re: Another form of SQL injection

howachen@gmail. com wrote:[color=blue]
> Hi,[/color]
<snip>[color=blue]
> However, another form of SQL injection might in the form of...
>
> /*************** *************** *************** *************/
> $name = "1 UNION blah blah blah"
> $query = "SELECT * FROM users WHERE id = ".$name;
> /*************** *************** *************** *************/
>
> for case 1, we can easily solved by escaping the special characters
> like " ' ", but how to solve for case 2?[/color]

Use prepared statements. FWIW,
<http://ilia.ws/archives/103-mysql_real_esca pe_string-versus-Prepared-Statements.html >

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

[howachen@gmail.com]

Re: Another form of SQL injection


Mladen Gogala 寫道：
[color=blue]
> howachen@gmail. com wrote:[color=green]
> > Hi,
> >
> > In many web articles, people focusing on SQL injection in the form of :
> >
> >
> > e.g.
> > /*************** *************** *************** *************/
> > $name = "tom' UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE name = '".$name."';
> > /*************** *************** *************** *************/
> >
> > However, another form of SQL injection might in the form of...
> >
> > /*************** *************** *************** *************/
> > $name = "1 UNION blah blah blah"
> > $query = "SELECT * FROM users WHERE id = ".$name;
> > /*************** *************** *************** *************/
> >
> > for case 1, we can easily solved by escaping the special characters
> > like " ' ", but how to solve for case 2?
> >
> > Thanks.
> >[/color]
>
>
> This dynamic query forming is the root cause of the problem.
> Here is how to solve it:
>
> require_once ('adodb/adodb.inc.php') ;
> require_once ('adodb/adodb-exceptions.inc. php');
>
> $db = NewADOConnectio n("oci8");
> $SQL="SELECT * FROM users WHERE name = :NAME";
> try {
> $db->Connect($DSN['database'], $DSN['username'],$DSN['password']);
> $db->execute($SQL,a rray("NAME"=>$n ame));
> }
> catch(Exception $e) {
> die($e->getMessage() );
> }
>
> The only difference from your situation is the use of "oci8" driver
> which is unlikely to work with MySQL. Everything else will work,
> including placeholders. That way, you not only eliminate any possibility
> for SQL injection but you also help the underlying database as it can
> reuse previously parsed cursor. Entering SQL expressions will not have
> any effect, as the variable is bound to a placeholder, not used to
> create SQL dynamically.
> --
> Mladen Gogala
> http://www.mgogala.com[/color]

hello,

how to use placeholder without adodb ?

(PHP4)

thanks...

[Mladen Gogala]

Re: Another form of SQL injection

howachen@gmail. com wrote:[color=blue]
> Mladen Gogala 寫道：[/color]

One and only.
[color=blue]
> hello,
>
> how to use placeholder without adodb ?
>
> (PHP4)
>
> thanks...
>[/color]

PDO also supports placeholders.

--
Mladen Gogala

http://www.mgogala.com

[howachen@gmail.com]

Re: Another form of SQL injection


Mladen Gogala 寫道：
[color=blue]
> howachen@gmail. com wrote:[color=green]
> > Mladen Gogala 寫道：[/color]
>
> One and only.
>[color=green]
> > hello,
> >
> > how to use placeholder without adodb ?
> >
> > (PHP4)
> >
> > thanks...
> >[/color]
>
> PDO also supports placeholders.
>
> --
> Mladen Gogala
> http://www.mgogala.com[/color]

well, PDO only ship with PHP5, what is the best practice for DB query
in PHP4?

thanks anyway...