Re: solution for preventing injection attacks

www.douglassdavis.com (doug@douglassdavis.com) wrote:

: I have an idea for preventing sql injection attacks, however it would
: have to be implemented by the database vendor. Let me know if I am on
: the right track, this totally off base, or already implemented
: somewhere...

: Lets say you could have a format string such as in printf

: $format=" SELECT %s FROM %s WHERE id='%s' ";
: $fieldname="last_name";
: $tablename="personel";
: $id="425";

: and you could execute a query like

: mysql_query_formatted($format, $fieldname, $tablename, $id);

: now, the key is that instead of just adding the $fieldname, $tablename,
: $id to the $format string and passing it to mysql_query, it would be
: passed to the parser as separate strings. The parser should know how
: to handle that format. That way, the parser would always know where
: the different tables names, field names, and other strings start and
: end. So, the problem of injection attacks caused by some one confusing
: the parser by entering things like ' and " is gone.


: It would be easier on the programmer. There would be no need to worry
: about escape characters when passing to this function, the strings
: would not have to be escaped.. The parser would no longer have to
: guess where the boundaries are. No more worrying about injection
: attacks.

: does that make sense?


Yes it makes a lot of sense, they are called "bind variables", and they
are implemented by vendors such as Oracle.

Quoting myself from an earlier post

oracle examples


http://www.oracle.com/ technology/ pub/ articles/
oracle_php_cookbook/ ullman_bindings.html


mysql via mysqli (look for "bind")


http://ca.php.net/mysqli


mysql without mysqli



Andy Hassall
Sep 6, 2:28 pm show options
...
I recommend using the ADOdb library
(http://adodb.sourceforge.net/).





--

This programmer available for rent.

Re: solution for preventing injection attacks

www.douglassdavis.com (doug@douglassdavis.com) wrote:

: I have an idea for preventing sql injection attacks, however it would
: have to be implemented by the database vendor. Let me know if I am on
: the right track, this totally off base, or already implemented
: somewhere...

: Lets say you could have a format string such as in printf

: $format=" SELECT %s FROM %s WHERE id='%s' ";
: $fieldname="last_name";
: $tablename="personel";
: $id="425";

: and you could execute a query like

: mysql_query_formatted($format, $fieldname, $tablename, $id);

: now, the key is that instead of just adding the $fieldname, $tablename,
: $id to the $format string and passing it to mysql_query, it would be
: passed to the parser as separate strings. The parser should know how
: to handle that format. That way, the parser would always know where
: the different tables names, field names, and other strings start and
: end. So, the problem of injection attacks caused by some one confusing
: the parser by entering things like ' and " is gone.


: It would be easier on the programmer. There would be no need to worry
: about escape characters when passing to this function, the strings
: would not have to be escaped.. The parser would no longer have to
: guess where the boundaries are. No more worrying about injection
: attacks.

: does that make sense?


Yes it makes a lot of sense, they are called "bind variables", and they
are implemented by vendors such as Oracle.

Quoting myself from an earlier post

oracle examples


http://www.oracle.com/ technology/ pub/ articles/
oracle_php_cookbook/ ullman_bindings.html


mysql via mysqli (look for "bind")


http://ca.php.net/mysqli


mysql without mysqli



Andy Hassall
Sep 6, 2:28 pm show options
...
I recommend using the ADOdb library
(http://adodb.sourceforge.net/).





--

This programmer available for rent.

solution for preventing injection attacks

I have an idea for preventing sql injection attacks, however it would
have to be implemented by the database vendor. Let me know if I am on
the right track, this totally off base, or already implemented
somewhere...

Lets say you could have a format string such as in printf

$format=" SELECT %s FROM %s WHERE id='%s' ";
$fieldname="last_name";
$tablename="personel";
$id="425";

and you could execute a query like

mysql_query_formatted($format, $fieldname, $tablename, $id);

now, the key is that instead of just adding the $fieldname, $tablename,
$id to the $format string and passing it to mysql_query, it would be
passed to the parser as separate strings. The parser should know how
to handle that format. That way, the parser would always know where
the different tables names, field names, and other strings start and
end. So, the problem of injection attacks caused by some one confusing
the parser by entering things like ' and " is gone.


It would be easier on the programmer. There would be no need to worry
about escape characters when passing to this function, the strings
would not have to be escaped.. The parser would no longer have to
guess where the boundaries are. No more worrying about injection
attacks.

does that make sense?


--
http://www.douglassdavis.com

Re: solution for preventing injection attacks

>I have an idea for preventing sql injection attacks, however it would
>have to be implemented by the database vendor. Let me know if I am on
>the right track, this totally off base, or already implemented
>somewhere...

Ok.

>Lets say you could have a format string such as in printf
>
>$format=" SELECT %s FROM %s WHERE id='%s' ";
>$fieldname="last_name";
>$tablename="personel";
>$id="425";
>
>and you could execute a query like
>
>mysql_query_formatted($format, $fieldname, $tablename, $id);

What about more elaborate queries that involve multiple tables, compted
columns, subqueries, updates, deletes, etc. ?

>now, the key is that instead of just adding the $fieldname, $tablename,
>$id to the $format string and passing it to mysql_query, it would be
>passed to the parser as separate strings. The parser should know how
>to handle that format. That way, the parser would always know where
>the different tables names, field names, and other strings start and
>end. So, the problem of injection attacks caused by some one confusing
>the parser by entering things like ' and " is gone.
>
>It would be easier on the programmer. There would be no need to worry
>about escape characters when passing to this function, the strings
>would not have to be escaped.. The parser would no longer have to
>guess where the boundaries are. No more worrying about injection
>attacks.
>
>does that make sense?

I hate to break it to you, but this is what stored procedures were made
for. I realize that procs are new to MySQL in v5.0, but if you have
access to them you should use them. They'll only accept a specific
list of required (and optional) parameters and the type checking and
conversions are handled automatically (most of the time). All you need
then is to make sure to escape text and close it in quotes and you can
build a query to execute the proc -- which follows a very simply
pattern no matter what the query ultimately does.

Re: solution for preventing injection attacks

>I have an idea for preventing sql injection attacks, however it would
>have to be implemented by the database vendor. Let me know if I am on
>the right track, this totally off base, or already implemented
>somewhere...

Ok.

>Lets say you could have a format string such as in printf
>
>$format=" SELECT %s FROM %s WHERE id='%s' ";
>$fieldname="last_name";
>$tablename="personel";
>$id="425";
>
>and you could execute a query like
>
>mysql_query_formatted($format, $fieldname, $tablename, $id);

What about more elaborate queries that involve multiple tables, compted
columns, subqueries, updates, deletes, etc. ?

>now, the key is that instead of just adding the $fieldname, $tablename,
>$id to the $format string and passing it to mysql_query, it would be
>passed to the parser as separate strings. The parser should know how
>to handle that format. That way, the parser would always know where
>the different tables names, field names, and other strings start and
>end. So, the problem of injection attacks caused by some one confusing
>the parser by entering things like ' and " is gone.
>
>It would be easier on the programmer. There would be no need to worry
>about escape characters when passing to this function, the strings
>would not have to be escaped.. The parser would no longer have to
>guess where the boundaries are. No more worrying about injection
>attacks.
>
>does that make sense?

I hate to break it to you, but this is what stored procedures were made
for. I realize that procs are new to MySQL in v5.0, but if you have
access to them you should use them. They'll only accept a specific
list of required (and optional) parameters and the type checking and
conversions are handled automatically (most of the time). All you need
then is to make sure to escape text and close it in quotes and you can
build a query to execute the proc -- which follows a very simply
pattern no matter what the query ultimately does.

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> now, the key is that instead of just adding the $fieldname, $tablename,
> $id to the $format string and passing it to mysql_query, it would be
> passed to the parser as separate strings. The parser should know how
> to handle that format. That way, the parser would always know where
> the different tables names, field names, and other strings start and
> end. So, the problem of injection attacks caused by some one confusing
> the parser by entering things like ' and " is gone.

Well, just write your own function that performs that. I have suggested
the following some time earlier:

function sql() {
$args = func_get_args();
$format = array_shift($args);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_string($args[$i]);
}
return vsprintf($format, $args);
}

$sql = sql("SELECT * FROM CowBrains WHERE fkCow = %d AND name = '%s'",
$id, $name);

If used consistently, dynamic strings in SQL statement will always be
escaped.

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> now, the key is that instead of just adding the $fieldname, $tablename,
> $id to the $format string and passing it to mysql_query, it would be
> passed to the parser as separate strings. The parser should know how
> to handle that format. That way, the parser would always know where
> the different tables names, field names, and other strings start and
> end. So, the problem of injection attacks caused by some one confusing
> the parser by entering things like ' and " is gone.

Well, just write your own function that performs that. I have suggested
the following some time earlier:

function sql() {
$args = func_get_args();
$format = array_shift($args);
for($i = 0, $l = count($args); $i < $l; $i++) {
$args[$i] = mysql_escape_string($args[$i]);
}
return vsprintf($format, $args);
}

$sql = sql("SELECT * FROM CowBrains WHERE fkCow = %d AND name = '%s'",
$id, $name);

If used consistently, dynamic strings in SQL statement will always be
escaped.

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> I have an idea for preventing sql injection attacks, however it would
> have to be implemented by the database vendor. Let me know if I am on
> the right track, this totally off base, or already implemented
> somewhere...

They already exist. In some languages, AFAICS, they are called
"Parameterized queries". Very neat.

Cheers,
Nicholas Sherlock

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> I have an idea for preventing sql injection attacks, however it would
> have to be implemented by the database vendor. Let me know if I am on
> the right track, this totally off base, or already implemented
> somewhere...

They already exist. In some languages, AFAICS, they are called
"Parameterized queries". Very neat.

Cheers,
Nicholas Sherlock

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> I have an idea for preventing sql injection attacks, however it would
> have to be implemented by the database vendor. Let me know if I am on
> the right track, this totally off base, or already implemented
> somewhere...
>
> Lets say you could have a format string such as in printf
>
> $format=" SELECT %s FROM %s WHERE id='%s' ";
> $fieldname="last_name";
> $tablename="personel";
> $id="425";
>
> and you could execute a query like
>
> mysql_query_formatted($format, $fieldname, $tablename, $id);

I know them as prepared statements and they are looking like this:

INSERT INTO table (attr1,attr2,...,attrN) VALUES (?,?,?,?,?,...,?)

And they are filled like this:

preparedStatement->setString(pos, string) or
preparedStatement->setBoolean(pos, bool) or ...

So the preparedstatement functions handle each type as they have to (e.g. escaping strings
if necessary and adding 's to the start and end)

Regards
Stefan

>...

Re: solution for preventing injection attacks

www.douglassdavis.com wrote:
> I have an idea for preventing sql injection attacks, however it would
> have to be implemented by the database vendor. Let me know if I am on
> the right track, this totally off base, or already implemented
> somewhere...
>
> Lets say you could have a format string such as in printf
>
> $format=" SELECT %s FROM %s WHERE id='%s' ";
> $fieldname="last_name";
> $tablename="personel";
> $id="425";
>
> and you could execute a query like
>
> mysql_query_formatted($format, $fieldname, $tablename, $id);

I know them as prepared statements and they are looking like this:

INSERT INTO table (attr1,attr2,...,attrN) VALUES (?,?,?,?,?,...,?)

And they are filled like this:

preparedStatement->setString(pos, string) or
preparedStatement->setBoolean(pos, bool) or ...

So the preparedstatement functions handle each type as they have to (e.g. escaping strings
if necessary and adding 's to the start and end)

Regards
Stefan

>...