Scrubbing MySQL Values and CSVtoArray()

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

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

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

$csvValues is already scrubbed for some business logic, but (obviously)
it needs to have that mysql_real_escape 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 "architectural reasons"
(for this and some other stuff, too) would evaporate if someone could
help me write a function like this:

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

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman =
=

wrote:

> I have a function that passes a csv string to mysql to use as values:
>
> > function fnINSERT ($csvValues) {
> $sSQL =3D "INSERT INTO mytable (
> field_a, field_b, field_b, field_c
> ) VALUES {
> $csvValues);
> return mysql_query($sSQL);
> }
>
> //note the SQL Injection attack in the 2nd parameter
> $csvValues =3D "1, "'1' OR ''=3D'"
> $oResult =3D fnINSERT($csvValues);
> ?>
>
> $csvValues is already scrubbed for some business logic, but (obviously=
) =

> it needs to have that mysql_real_escape function run on each of those =
=

> csv values, as well.
>
> For architectural reasons, I can't do the scrubbing before the functio=
n =

> 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 "architectural reason=
s" =

> (for this and some other stuff, too) would evaporate if someone could =
=

> help me write a function like this:
>
> function CSVtoArray($sCSV) {
> $aryRetVal =3D array();
> $aryRetVal =3D foo($sCSV);
> return $aryRetVal;
> }


Well, there's one in the making or something: =

, it's not in my P=
HP =

though.

You could define a stream to a variable to get fgetcsv() to work for you=
, =

might be some overkill.

In there are some =

efforts to get it right, which one you choose depends on the exact needs=
..
-- =

Rik Wasmus

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 03:00:50 +0200, Rik wro=
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 "architectural =

>> reasons" (for this and some other stuff, too) would evaporate if =

>> someone could help me write a function like this:
>>
>> function CSVtoArray($sCSV) {
>> $aryRetVal =3D array();
>> $aryRetVal =3D foo($sCSV);
>> return $aryRetVal;
>> }
>
>
> Well, there's one in the making or something: =

> , 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 =3D ',', $enclosure =3D '"', $len =3D=
0)
{
$fh =3D fopen('php://memory', 'w+');
fwrite($fh, $str);
rewind($fh);
$result =3D fgetcsv( $fh, $len, $delimiter, $enclosure );
fclose($fh);
return $result;
}
var_dump(parseCSV('"foo","bar\"",234,324,"boz"'));


-- =

Rik Wasmus

Re: Scrubbing MySQL Values and CSVtoArray()

Rik wrote:
> On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman

>> 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 "architectural
>> reasons" (for this and some other stuff, too) would evaporate if
>> someone could help me write a function like this:
>>
>> function CSVtoArray($sCSV) {
>> $aryRetVal = array();
>> $aryRetVal = foo($sCSV);
>> return $aryRetVal;
>> }
>
>
> Well, there's one in the making or something:
> , 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 there 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.

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(parseCSV('"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.

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(parseCSV('"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.

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 03:56:22 +0200, Sanders Kaufman =
=

wrote:

> Rik wrote:
>> On Fri, 17 Aug 2007 02:46:55 +0200, Sanders Kaufman t>
>
>>> 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 fi=
le =

> 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 neede=
d, =

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 "architectural =

>>> reasons" (for this and some other stuff, too) would evaporate if =

>>> someone could help me write a function like this:
>>>
>>> function CSVtoArray($sCSV) {
>>> $aryRetVal =3D array();
>>> $aryRetVal =3D foo($sCSV);
>>> return $aryRetVal;
>>> }
>> Well, there's one in the making or something: =

>> , it's not in m=
y =

>> 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:
Stream-functions:
Making your own: =



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 there 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 fo=
r =

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

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 04:06:46 +0200, Sanders Kaufman =
=

wrote:

> Rik wrote:
>
>> Hmmmz, someone posted an interesting solution:
>> function parseCSV($str, $delimiter =3D ',', $enclosure =3D '"', $len=
=3D 0)
>> {
>> $fh =3D fopen('php://memory', 'w+');
>> fwrite($fh, $str);
>> rewind($fh);
>> $result =3D fgetcsv( $fh, $len, $delimiter, $enclosure );
>> fclose($fh);
>> return $result;
>> }
>> var_dump(parseCSV('"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.


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 strin=
g, =

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 f=
or =

it, all possible with giving it a single target, and the same code logs =
to =

it without any problems.
-- =

Rik Wasmus

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/

Re: Scrubbing MySQL Values and CSVtoArray()

..oO(Rik)

>On Fri, 17 Aug 2007 03:00:50 +0200, Rik wrote:
>>
>> Well, there's one in the making or something:
>> , 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(parseCSV('"foo","bar\"",234,324,"boz"'));

Clever. Ugly, but clever. ;)

Micha

Re: Scrubbing MySQL Values and CSVtoArray()

On Fri, 17 Aug 2007 18:13:51 +0200, Michael Fesser wrot=
e:

> .oO(Rik)
>
>> On Fri, 17 Aug 2007 03:00:50 +0200, Rik =
=

>> wrote:
>>>
>>> Well, there's one in the making or something:
>>> , 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 th=
e
> 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 cla=
ss
> "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 =3D ',', $enclosure =3D '"', $len =
=3D 0)
>> {
>> >> }
> Clever. Ugly, but clever. ;)

Indeed :P
-- =

Rik Wasmus