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($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;
}
10  1942 
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($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.
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 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:
<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
On Fri, 17 Aug 2007 03:00:50 +0200, Rik <lu************@hotmail.comwrote:
>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:
<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(parseCSV('"foo","bar\"",234,324,"boz"'));
--
Rik Wasmus
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 "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:
<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.
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.
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.
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 "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:
<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
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(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 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
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/
..oO(Rik)
>On Fri, 17 Aug 2007 03:00:50 +0200, Rik <lu************@hotmail.comwrote:
>>
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(parseCSV('"foo","bar\"",234,324,"boz"')) ;
Clever. Ugly, but clever. ;)
Micha
On Fri, 17 Aug 2007 18:13:51 +0200, Michael Fesser <ne*****@gmx.dewrote:
.oO(Rik)
>On Fri, 17 Aug 2007 03:00:50 +0200, Rik <lu************@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Simon |
last post by:
Hi,
I am having a little problem with my PHP - MySQl code, I have two tables
(shown below) and I am trying populate a template page with data from both.
<disclaimer>Now I would like to say my...
|
by: Marcus |
last post by:
Hello, quick question about MySQL storing NULL values...
Say I have a textbox called $_POST and a variable $var.
if(empty($_POST))
$var = NULL;
else
$var = $_POST;
Disregarding...
|
by: Neculai Macarie |
last post by:
Hi!
Using Union and Order By gives strange behaviour in the following test-case:
drop table if exists gallery;
drop table if exists gallery_categ;
# create test tables
create table gallery...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest...
|
by: Saqib Ali |
last post by:
I have created 2 tables in my MySQL database. A_TAB and B_TAB.
They have auto-incrementing integer primary keys respectively named
A_ID & B_ID.
When I created B_TAB, I declared a field named A_ID...
|
by: Steve |
last post by:
I have a million record mainframe flat file that I BULK INSERT into a
SQL table with CHAR(fieldlength) deined for every column to prevent
import errors.
Once imported I "INSERT INTO ... SELECT...
|
by: Karam Chand |
last post by:
Hello
I have been working with Access and MySQL for pretty
long time. Very simple and able to perform their jobs.
I dont need to start a flame anymore :)
I have to work with PGSQL for my...
|
by: Dan Lewis |
last post by:
I've imported a ms access database into a table in a mysql database.
The access database contains a field that holds date/time values in
'general date' format. These all show up at 01/01/1970 in...
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
