Scrubbing MySQL Values and CSVtoArray()

[Sanders Kaufman]

I have a function that passes a csv string to mysql to use as values:

<?php
function fnINSERT ($csvValues) {
$sSQL = "INSERT INTO mytable (
field_a, field_b, field_b, field_c
) VALUES {
$csvValues);
return mysql_query($sS QL);
}

//note the SQL Injection attack in the 2nd parameter
$csvValues = "1, "'1' OR ''='"
$oResult = fnINSERT($csvVa lues);
?>

$csvValues is already scrubbed for some business logic, but (obviously)
it needs to have that mysql_real_esca pe function run on each of those
csv values, as well.

For architectural reasons, I can't do the scrubbing before the function
is called, but instead have to do it when it comes to me as this csv SLOB.

My First Question:
Can I run the real escape function on $csvValues as a whole, to
successfully scrub each parameter - or will I experience undesirable
results that way?

My Second Question:
I can convert an array to csv pretty easily, but going the other way
screws me up (because of quoted commas). So, my "architectu ral reasons"
(for this and some other stuff, too) would evaporate if someone could
help me write a function like this:

function CSVtoArray($sCS V) {
$aryRetVal = array();
$aryRetVal = foo($sCSV);
return $aryRetVal;
}

[Rik]

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bucky@kaufman. net
wrote:

I have a function that passes a csv string to mysql to use as values:
>
<?php
function fnINSERT ($csvValues) {
$sSQL = "INSERT INTO mytable (
field_a, field_b, field_b, field_c
) VALUES {
$csvValues);
return mysql_query($sS QL);
}
>
//note the SQL Injection attack in the 2nd parameter
$csvValues = "1, "'1' OR ''='"
$oResult = fnINSERT($csvVa lues);
?>
>
$csvValues is already scrubbed for some business logic, but (obviously)
it needs to have that mysql_real_esca pe function run on each of those
csv values, as well.
>
For architectural reasons, I can't do the scrubbing before the function
is called, but instead have to do it when it comes to me as this csv
SLOB.

Which shouldn't be the case...

My First Question:
Can I run the real escape function on $csvValues as a whole, to
successfully scrub each parameter - or will I experience undesirable
results that way?

If there are strings in it: yes, you'll have undesired results.

My Second Question:
I can convert an array to csv pretty easily, but going the other way
screws me up (because of quoted commas). So, my "architectu ral reasons"
(for this and some other stuff, too) would evaporate if someone could
help me write a function like this:
>
function CSVtoArray($sCS V) {
$aryRetVal = array();
$aryRetVal = foo($sCSV);
return $aryRetVal;
}

Well, there's one in the making or something:
<http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my PHP
though.

You could define a stream to a variable to get fgetcsv() to work for you,
might be some overkill.

In <http://www.php.net/manual/en/function.split. phpthere are some
efforts to get it right, which one you choose depends on the exact needs..
--
Rik Wasmus

[Rik]

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 03:00:50 +0200, Rik <luiheidsgoeroe @hotmail.comwro te:

>My Second Question:
>I can convert an array to csv pretty easily, but going the other way
>screws me up (because of quoted commas). So, my "architectu ral
>reasons" (for this and some other stuff, too) would evaporate if
>someone could help me write a function like this:
>>
>function CSVtoArray($sCS V) {
> $aryRetVal = array();
> $aryRetVal = foo($sCSV);
> return $aryRetVal;
>}

>
>
Well, there's one in the making or something:
<http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my
PHP though.
>
You could define a stream to a variable to get fgetcsv() to work for
you, might be some overkill.

Hmmmz, someone posted an interesting solution:

function parseCSV($str, $delimiter = ',', $enclosure = '"', $len =0)
{
$fh = fopen('php://memory', 'w+');
fwrite($fh, $str);
rewind($fh);
$result = fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
}
var_dump(parseC SV('"foo","bar\ "",234,324,"boz "'));


--
Rik Wasmus

[Sanders Kaufman]

Re: Scrubbing MySQL Values and CSVtoArray()

Rik wrote:

On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bucky@kaufman. net>

>For architectural reasons, I can't do the scrubbing before the
>function is called, but instead have to do it when it comes to me as
>this csv SLOB.

>
Which shouldn't be the case...

Should, shmould. In this case, I absolutely must keep the business
logic separate from the database logic.

I have a database.php file that does *all* of the database work, and
then a base class that does the business logic. But if I have to put
the mysql-specific scrubbing function in the business logic base class -
it defeats the purpose of putting ALL of the database work in database.php.

The idea is that I can just replace the mysql-specific database.php file
with a Postgre or file system or whatever else database, to support
whatever db I happen to be using at the time.

>My First Question:
>Can I run the real escape function on $csvValues as a whole, to
>successfully scrub each parameter - or will I experience undesirable
>results that way?

>
If there are strings in it: yes, you'll have undesired results.

I figured - but I had to ask.

>My Second Question:
>I can convert an array to csv pretty easily, but going the other way
>screws me up (because of quoted commas). So, my "architectu ral
>reasons" (for this and some other stuff, too) would evaporate if
>someone could help me write a function like this:
>>
>function CSVtoArray($sCS V) {
> $aryRetVal = array();
> $aryRetVal = foo($sCSV);
> return $aryRetVal;
>}

>
>
Well, there's one in the making or something:
<http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my
PHP though.
>
You could define a stream to a variable to get fgetcsv() to work for
you, might be some overkill.

"Define a stream"? Wassat?

>
In <http://www.php.net/manual/en/function.split. phpthere are some
efforts to get it right, which one you choose depends on the exact needs.

Wow. This is a *much* bigger deal than I thought.

Fortunately, it looks like I found a fix - by just structuring my code
better.

I was trying to convert an array into a csv, pass it to another
function, and then break it back out into an array. Too many
unnecessary levels of abstraction pretty much guarantees failure, don't it?

My problem solves itself if I just keep it as an array until
*immediately* before composing my sql statement - and THEN scrub the
elements as I do so.

Still - a nice CSCtoArray() function would be cool.

[Sanders Kaufman]

Re: Scrubbing MySQL Values and CSVtoArray()

Rik wrote:

Hmmmz, someone posted an interesting solution:
>
function parseCSV($str, $delimiter = ',', $enclosure = '"', $len = 0)
{
$fh = fopen('php://memory', 'w+');
fwrite($fh, $str);
rewind($fh);
$result = fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
}
var_dump(parseC SV('"foo","bar\ "",234,324,"boz "'));

Weird. I've never seen "php://memory" before. The rest is pretty wild,
too. It looks like I've got some book-learning to do.

[Sanders Kaufman]

Re: Scrubbing MySQL Values and CSVtoArray()

Rik wrote:

Hmmmz, someone posted an interesting solution:
>
function parseCSV($str, $delimiter = ',', $enclosure = '"', $len = 0)
{
$fh = fopen('php://memory', 'w+');
fwrite($fh, $str);
rewind($fh);
$result = fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
}
var_dump(parseC SV('"foo","bar\ "",234,324,"boz "'));

Oh, I get it! PHP can parse a CSV *file*, but not a CSV *string*. So,
to parse the string, he just created a file in memory, and parsed that.

That's cool. I'll bet there are other cool ways to make use of that
technique.

[Rik]

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 03:56:22 +0200, Sanders Kaufman <bucky@kaufman. net
wrote:

Rik wrote:

>On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bucky@kaufman. net>

>

>>For architectural reasons, I can't do the scrubbing before the
>>function is called, but instead have to do it when it comes to me as
>>this csv SLOB.

> Which shouldn't be the case...

>
Should, shmould. In this case, I absolutely must keep the business
logic separate from the database logic.

Well, that's OK. Why the hell it's a CSV string instead of the raw data is
another question :P

I have a database.php file that does *all* of the database work, and
then a base class that does the business logic. But if I have to put
the mysql-specific scrubbing function in the business logic base class-
it defeats the purpose of putting ALL of the database work in
database.php.
>
The idea is that I can just replace the mysql-specific database.php file
with a Postgre or file system or whatever else database, to support
whatever db I happen to be using at the time.

And that's the point where it might be turned into a CVS string if needed,
not in your business logic.

>>My Second Question:
>>I can convert an array to csv pretty easily, but going the other way
>>screws me up (because of quoted commas). So, my "architectu ral
>>reasons" (for this and some other stuff, too) would evaporate if
>>someone could help me write a function like this:
>>>
>>function CSVtoArray($sCS V) {
>> $aryRetVal = array();
>> $aryRetVal = foo($sCSV);
>> return $aryRetVal;
>>}

> Well, there's one in the making or something:
><http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my
>PHP though.
> You could define a stream to a variable to get fgetcsv() to work for
>you, might be some overkill.

>
"Define a stream"? Wassat?

Streams: <http://nl3.php.net/manual/en/wrappers.php>
Stream-functions: <http://nl3.php.net/manual/en/ref.stream.php>
Making your own:
<http://nl3.php.net/manual/en/function.stream-wrapper-register.php>

Just forgot about the ability to abuse php://memory instead of going
through the pain of writing a whole wrapper for a single scalar variable..

> In <http://www.php.net/manual/en/function.split. phpthere are some
>efforts to get it right, which one you choose depends on the exact
>needs.

>
Wow. This is a *much* bigger deal than I thought.

I'm equally amazed PHP still hasn't got simple built-in functionality for
this. It's not like CVS is rare...

Fortunately, it looks like I found a fix - by just structuring my code
better.
>
I was trying to convert an array into a csv, pass it to another
function, and then break it back out into an array. Too many
unnecessary levels of abstraction pretty much guarantees failure, don't
it?
>
My problem solves itself if I just keep it as an array until
*immediately* before composing my sql statement - and THEN scrub the
elements as I do so.

Yup, that's what I was trying to say with the first 'Which shouldn't be
the case' :)

Still - a nice CSVtoArray() function would be cool.

Indeed.
--
Rik Wasmus

[Rik]

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 04:06:46 +0200, Sanders Kaufman <bucky@kaufman. net
wrote:

Rik wrote:
>

>Hmmmz, someone posted an interesting solution:
> function parseCSV($str, $delimiter = ',', $enclosure = '"', $len= 0)
>{
> $fh = fopen('php://memory', 'w+');
> fwrite($fh, $str);
> rewind($fh);
> $result = fgetcsv( $fh, $len, $delimiter, $enclosure );
> fclose($fh);
> return $result;
>}
>var_dump(parse CSV('"foo","bar \"",234,324,"bo z"'));

>
Oh, I get it! PHP can parse a CSV *file*, but not a CSV *string*. So,
to parse the string, he just created a file in memory, and parsed that..
>
That's cool. I'll bet there are other cool ways to make use of that
technique.

Yup, one of the main advantages is 'directing' output. Say for instance I
have a logger class. I can set the output where to log in a single string,
making it quite versatile. Log to the screen, a systemfile, a file on a
ftpserver, to some socket; hell, even a database if I define a wrapper for
it, all possible with giving it a single target, and the same code logs to
it without any problems.
--
Rik Wasmus

[Toby A Inkster]

Re: Scrubbing MySQL Values and CSVtoArray()

Sanders Kaufman wrote:

I have a database.php file that does *all* of the database work, and
then a base class that does the business logic. But if I have to put
the mysql-specific scrubbing function in the business logic base class -
it defeats the purpose of putting ALL of the database work in database.php.

The solution is not to put the MySQL-scrubbing into the business logic
class, but to have the business logic class return an array (or,
even better: object) instead of a CSV string. Then the database class can
easily perform database-specific scrubbing mechanisms on the data before
inserting it into the database.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 57 days, 14:16.]

Elvis

http://tobyinkster.co.uk/blog/2007/08/16/elvis/

[Michael Fesser]

Re: Scrubbing MySQL Values and CSVtoArray()

..oO(Rik)

>On Fri, 17 Aug 2007 03:00:50 +0200, Rik <luiheidsgoeroe @hotmail.comwro te:

>>
>Well, there's one in the making or something:
><http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my
>PHP though.

It's already in CVS (since 8 months or so), but obviously not in the
current branches. One could use function_exists () to check for it and
implement a custom str-getcsv() function if necessary, using one of the
ways described below.

>You could define a stream to a variable to get fgetcsv() to work for
>you, might be some overkill.

The manual for stream_wrapper_ register() contains a little example class
"VariableStream " to access global variables. This could be useful here
(should even work with PHP 4).

>Hmmmz, someone posted an interesting solution:
>
>function parseCSV($str, $delimiter = ',', $enclosure = '"', $len = 0)
>{
$fh = fopen('php://memory', 'w+');
fwrite($fh, $str);
rewind($fh);
$result = fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
>}
>var_dump(parse CSV('"foo","bar \"",234,324,"bo z"'));

Clever. Ugly, but clever. ;)

Micha

[Rik]

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 18:13:51 +0200, Michael Fesser <netizen@gmx.de wrote:

.oO(Rik)
>

>On Fri, 17 Aug 2007 03:00:50 +0200, Rik <luiheidsgoeroe @hotmail.com
>wrote:

>>>
>>Well, there's one in the making or something:
>><http://nl3.php.net/manual/en/function.str-getcsv.php>, it's not in my
>>PHP though.

>
It's already in CVS (since 8 months or so), but obviously not in the
current branches. One could use function_exists () to check for it and
implement a custom str-getcsv() function if necessary, using one of the
ways described below.
>

>>You could define a stream to a variable to get fgetcsv() to work for
>>you, might be some overkill.

>
The manual for stream_wrapper_ register() contains a little example class
"VariableStream " to access global variables. This could be useful here
(should even work with PHP 4).

Would be, allthough making the variable a global just for that is about as
ugly as using php://memory

>function parseCSV($str, $delimiter = ',', $enclosure = '"', $len = 0)
>{
> <SNIP using 'php://memory');
>}

Clever. Ugly, but clever. ;)

Indeed :P
--
Rik Wasmus