Smarter way to extract data from FORM and save to DB?

[Gilles Ganault]

Hello

Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:

======
//If 'id' set -update; Otherwise -insert
if($_POST['id'])
$sql = sprintf("UPDATE $table SET name='%s', tel='%s' WHERE
id=%s",$_POST['name'],$_POST['tel'],$_POST['id']);
else
$sql = sprintf("INSERT INTO $table (id,name,tel) VALUES
(NULL,'%s','%s' )",$_POST['name'],$_POST['tel']);
======

Thank you.

[Willem Bogaerts]

Re: Smarter way to extract data from FORM and save to DB?

Sure. You can do a lot with foreach, str_replace, array_map, etc.

Gilles Ganault wrote:

Hello
>
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:
>
======
//If 'id' set -update; Otherwise -insert
if($_POST['id'])
$sql = sprintf("UPDATE $table SET name='%s', tel='%s' WHERE
id=%s",$_POST['name'],$_POST['tel'],$_POST['id']);
else
$sql = sprintf("INSERT INTO $table (id,name,tel) VALUES
(NULL,'%s','%s' )",$_POST['name'],$_POST['tel']);
======
>
Thank you.

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.

http://www.kratz.nl/

[rf]

Re: Smarter way to extract data from FORM and save to DB?


"Gilles Ganault" <nospam@nospam. comwrote in message
news:q56oq3dk78 pl0539362fj1v7c n34gd66jd@4ax.c om...

Hello
>
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:
>
======
//If 'id' set -update; Otherwise -insert
if($_POST['id'])
$sql = sprintf("UPDATE $table SET name='%s', tel='%s' WHERE
id=%s",$_POST['name'],$_POST['tel'],$_POST['id']);
else
$sql = sprintf("INSERT INTO $table (id,name,tel) VALUES
(NULL,'%s','%s' )",$_POST['name'],$_POST['tel']);
======

Never heard of sql injection then?

With the above I could drop your entire database :-)

--
Richard.

[ELINTPimp]

Re: Smarter way to extract data from FORM and save to DB?

On Feb 8, 3:57 am, Gilles Ganault <nos...@nospam. comwrote:

Hello
>
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:
>
======
//If 'id' set -update; Otherwise -insert
if($_POST['id'])
$sql = sprintf("UPDATE $table SET name='%s', tel='%s' WHERE
id=%s",$_POST['name'],$_POST['tel'],$_POST['id']);
else
$sql = sprintf("INSERT INTO $table (id,name,tel) VALUES
(NULL,'%s','%s' )",$_POST['name'],$_POST['tel']);
======
>
Thank you.

Check out Zend Framework 1.5 (currently a 'preview release', but
coming out soon). Specifically, check out Zend_Form and Zend_Db.
There are other similar solutions out there to make the task of
mundane form creation and CRUD operations easier, but in my opinion,
Zend Framework will give you a better payoff from your investment in
learning it since it is a glue-type framework and doesn't require you
to initialize a large framework just to you parts and pieces.

Enjoy,
Steve

[Gilles Ganault]

Re: Smarter way to extract data from FORM and save to DB?

On Fri, 08 Feb 2008 10:04:48 +0100, Willem Bogaerts
<w.bogaerts@kra tz.maardanzonde rditstuk.nlwrot e:

>Sure. You can do a lot with foreach, str_replace, array_map, etc.

If someone has some working code handy, I'm interested. I'm not clear
on how to go from an array filled in a form, and then extracting each
key/value into an SQL query.

Thanks.

[Steve Foley]

Re: Smarter way to extract data from FORM and save to DB?

"rf" <rf@invalid.com wrote in message
news:hlVqj.1254 5$421.11446@new s-server.bigpond. net.au...

>
Never heard of sql injection then?

Not before you mentioned it. Thanks - that looks like some nasty stuff. I
quess I have some more work to do

>
With the above I could drop your entire database :-)

Ouch!!!

>
--
Richard.
>
>

[Steve]

Re: Smarter way to extract data from FORM and save to DB?

"Gilles Ganault" <nospam@nospam. comwrote in message
news:q56oq3dk78 pl0539362fj1v7c n34gd66jd@4ax.c om...

Hello
>
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:

here's one way...certainly not the only way. however, this demonstrates what
you ask...sorry for the text-wrapping:

<?
$date = strtotime('now' );
$date = date('m', $date) . '/01/' . date('Y', $date);
$date = $_REQUEST['date'] ? $_REQUEST['date'] : $date;
$date = strtotime($date );
$dealer = 'someFacility';

// define updateable fields and default their values
$financials = array(
'Center' =$dealer
,
'Name' =$dealerName
,
'Month' =date('m', $date)
,
'Year' =date('Y', $date)
,
'pbsMaterialInv entory' =0 ,
'bcsAdvertising ' =0
);

// used to create dynamic db update statements
function createUpdateSta tement(&$value, $key)
{
$value = $key . " = '" . $value . "'";
return $value;
}

// used to format field values
function formatValue(&$v alue, $key = '', $request = array())
{
if (!is_array($req uest)){ $request = array($request) ; }
if (in_array($key, array('Center', 'Name', 'Month', 'Year'))){ return; }
if ($request){ $value = $request[$key]; }
$value = number_format(g etInteger($valu e));
return $value;
}

// whatever $value is, format it as an integer regarless of locale...
// inval may truncate commas...we're just forcing correct truncation
function getInteger(&$va lue, $key = '', $request = array())
{
if (!is_array($req uest)){ $request = array($request) ; }
if (in_array($key, array('Center', 'Name', 'Month', 'Year'))){ return; }
if ($request){ $value = $request[$key]; }
$locale = localeconv();
$decimal = $locale['decimal_point'] . $locale['mon_decimal_po int'];
$negative = $locale['negative_sign'];
$value = intval(preg_rep lace('/[^0-9' . $decimal . $negative . ']/',
'', $value));
return $value;
}

// update $financials with values provided by user submit
array_walk($fin ancials, 'getInteger', $_REQUEST);

// sledge-hammer db update
$sql = "
DELETE
FROM financials
WHERE Center = '" . $dealer . "'
AND STR_TO_DATE(CON CAT(Year, '-', Month, '-01'), '%Y-%m-%d')
=
STR_TO_DATE('" . date('Y-m-d', $date) . "', '%Y-%m-%d')
";
db::execute($sq l);
$sql = "
INSERT INTO
financials
(
" . implode(",\r\n ", array_keys($fin ancials)) . "
)
VALUES
(
'" . implode("',\r\n '", $financials) . "'
)
";
db::execute($sq l);

// more gentle update where we have determined a record exists alread
array_walk($fin ancials, 'createUpdateSt atement');
$sql = "
UPDATE financials
SET
" . implode(",\r\n ", $supplement) . "
WHERE Center = '" . $dealer . "'
";
db::execute($sq l);

// now, verify in debug that data made it into the db
// having used either sledge-hammer or gentle upate
$sql = "
SELECT " . implode(",\r\n ",
array_keys($fin ancials)) . "
FROM financials
WHERE Center = '" . $dealer .
"'
AND STR_TO_DATE(CON CAT(Year, '-', Month, '-01'),
'%Y-%m-%d') =
STR_TO_DATE('" . date('Y-m-d', $date) . "',
'%Y-%m-%d')
";
$records = db::execute($sq l);
$financials = $records[0];
array_walk($fin ancials, 'formatValue');
echo '<pre style="font:10p x;">' . print_r($financ ials, true) . '</pre>';
?>

[Michael Fesser]

Re: Smarter way to extract data from FORM and save to DB?

..oO(Steve)

>"rf" <rf@invalid.com wrote in message
>

>Given the original post, correct.
>>
>But please tell me where the OP is going to use this variable? Is the OP
>going to feed this to mysql (or whatever) or just let it sitting around
>waiting for garbage collection? I suspect the former.
>>
>This *is* a snippit of code. We do not know what comes next.

>
>but i believe your claim was "with the above, i could drop your entire
>database". that was made regarding the original post. the claim is
>incorrect.

Hairsplitting.

>as for what comes next, who knows.

It's correct that the posted code alone can't be used for injection, but
it's quite obvious that the next step will _most likely_ be a query call
to the DB. There's not much else that would make sense. And since there
is no use of prepared statements either, the posted code is vulnerable
and can _most likely_ be abused for SQL injection and should be fixed.

Micha

[Gilles Ganault]

Re: Smarter way to extract data from FORM and save to DB?

On Fri, 08 Feb 2008 09:33:01 GMT, "rf" <rf@invalid.com wrote:

>Never heard of sql injection then?
>With the above I could drop your entire database :-)

I did hear, but wasn't paying attention because I first have to get
this CRUD thingie running ;-) Besides, although the web server will be
accessible from the Net, 1) it won't be published, meaning that if you
don't know the URL, there's no way to know it's there, and 2) I'll add
an .htaccess to prompt users for a login before they have access to
it.

But I'll read up on SQL injections because it looks nasty enough.
Thanks for pointing it out.

[Rik Wasmus]

Re: Smarter way to extract data from FORM and save to DB?

On Fri, 08 Feb 2008 09:57:04 +0100, Gilles Ganault <nospam@nospam. com
wrote:

Hello
>
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go through an array and
generate the SQL query in a couple of lines:
>
======
//If 'id' set -update; Otherwise -insert
if($_POST['id'])
$sql = sprintf("UPDATE $table SET name='%s', tel='%s' WHERE
id=%s",$_POST['name'],$_POST['tel'],$_POST['id']);
else
$sql = sprintf("INSERT INTO $table (id,name,tel) VALUES
(NULL,'%s','%s' )",$_POST['name'],$_POST['tel']);
======

A safer way would be prepared statements. This code is, seeing to the use
of sprintf(), very easily altered to use those.

--
Rik Wasmus

[Michael Fesser]

Re: Smarter way to extract data from FORM and save to DB?

..oO(Gilles Ganault)

>On Fri, 08 Feb 2008 09:33:01 GMT, "rf" <rf@invalid.com wrote:

>>Never heard of sql injection then?
>>With the above I could drop your entire database :-)

>
>I did hear, but wasn't paying attention because I first have to get
>this CRUD thingie running ;-) Besides, although the web server will be
>accessible from the Net, 1) it won't be published, meaning that if you
>don't know the URL, there's no way to know it's there

You shouldn't rely on that. There's a couple of ways how such a "hidden"
might leak (proxys, server logs, referrer, ...). It's just a matter of
them when someone will find it one way or another.

>and 2) I'll add
>an .htaccess to prompt users for a login before they have access to
>it.

Slightly better, but still allows the users to have some fun with your
database and perform unwanted actions.

>But I'll read up on SQL injections because it looks nasty enough.

That's the correct way. SQL injection is a serious issue and must be
fixed. It really helps to use prepared statements for example.

Micha