Another form of SQL injection

[howachen]

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]

[e-mail] wrote:

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.

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]

frizzle wrote:

[e-mail] wrote:

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.

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.

However, The resulting query is :

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

and it worked!

[Chung Leong]

ho******@gmail.com wrote:

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.

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]

Chung Leong wrote:

ho******@gmail.com wrote:

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.

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().

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]

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:

Chung Leong wrote:

ho******@gmail.com wrote:

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.

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().

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.

[Mladen Gogala]

ho******@gmail.com wrote:

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.

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 = NewADOConnection("oci8");
$SQL="SELECT * FROM users WHERE name = :NAME";
try {
$db->Connect($DSN['database'], $DSN['username'],$DSN['password']);
$db->execute($SQL,array("NAME"=>$name));
}
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]

frizzle wrote:

Chung Leong wrote:

ho******@gmail.com wrote:

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.

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().

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.

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]

ho******@gmail.com wrote:

Hi, <snip> 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?

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

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

[howachen]

Mladen Gogala 寫�：

ho******@gmail.com wrote:

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.

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 = NewADOConnection("oci8");
$SQL="SELECT * FROM users WHERE name = :NAME";
try {
$db->Connect($DSN['database'], $DSN['username'],$DSN['password']);
$db->execute($SQL,array("NAME"=>$name));
}
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

hello,

how to use placeholder without adodb ?

(PHP4)

thanks...

[Mladen Gogala]

ho******@gmail.com wrote:

Mladen Gogala 寫�：
One and only.
hello,

how to use placeholder without adodb ?

(PHP4)

thanks...

PDO also supports placeholders.

--
Mladen Gogala
http://www.mgogala.com

[howachen]

Mladen Gogala 寫�：

ho******@gmail.com wrote:

Mladen Gogala 寫�：

One and only.

hello,

how to use placeholder without adodb ?

(PHP4)

thanks...

PDO also supports placeholders.

--
Mladen Gogala
http://www.mgogala.com

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

thanks anyway...