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]

On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bu***@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]

On Fri, 17 Aug 2007 03:00:50 +0200, Rik <lu************ @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]

Rik wrote:

On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bu***@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]

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]

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]

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

Rik wrote:

>On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman <bu***@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]

On Fri, 17 Aug 2007 04:06:46 +0200, Sanders Kaufman <bu***@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]

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]

..oO(Rik)

>On Fri, 17 Aug 2007 03:00:50 +0200, Rik <lu************ @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