By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,278 Members | 1,359 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,278 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Feb 8 '08 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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/
Feb 8 '08 #2

P: n/a
rf

"Gilles Ganault" <no****@nospam.comwrote in message
news:q5********************************@4ax.com...
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.
Feb 8 '08 #3

P: n/a
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
Feb 8 '08 #4

P: n/a
On Fri, 08 Feb 2008 10:04:48 +0100, Willem Bogaerts
<w.********@kratz.maardanzonderditstuk.nlwrote:
>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.
Feb 8 '08 #5

P: n/a
"rf" <rf@invalid.comwrote in message
news:hl*******************@news-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.


Feb 8 '08 #6

P: n/a
"Gilles Ganault" <no****@nospam.comwrote in message
news:q5********************************@4ax.com...
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)
,
'pbsMaterialInventory' =0 ,
'bcsAdvertising' =0
);

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

// used to format field values
function formatValue(&$value, $key = '', $request = array())
{
if (!is_array($request)){ $request = array($request); }
if (in_array($key, array('Center', 'Name', 'Month', 'Year'))){ return; }
if ($request){ $value = $request[$key]; }
$value = number_format(getInteger($value));
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(&$value, $key = '', $request = array())
{
if (!is_array($request)){ $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_point'];
$negative = $locale['negative_sign'];
$value = intval(preg_replace('/[^0-9' . $decimal . $negative . ']/',
'', $value));
return $value;
}

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

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

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

// 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($financials)) . "
FROM financials
WHERE Center = '" . $dealer .
"'
AND STR_TO_DATE(CONCAT(Year, '-', Month, '-01'),
'%Y-%m-%d') =
STR_TO_DATE('" . date('Y-m-d', $date) . "',
'%Y-%m-%d')
";
$records = db::execute($sql);
$financials = $records[0];
array_walk($financials, 'formatValue');
echo '<pre style="font:10px;">' . print_r($financials, true) . '</pre>';
?>
Feb 8 '08 #7

P: n/a
..oO(Steve)
>"rf" <rf@invalid.comwrote 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
Feb 8 '08 #8

P: n/a
On Fri, 08 Feb 2008 09:33:01 GMT, "rf" <rf@invalid.comwrote:
>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.
Feb 9 '08 #9

P: n/a
On Fri, 08 Feb 2008 09:57:04 +0100, Gilles Ganault <no****@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
Feb 9 '08 #10

P: n/a
..oO(Gilles Ganault)
>On Fri, 08 Feb 2008 09:33:01 GMT, "rf" <rf@invalid.comwrote:
>>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
Feb 9 '08 #11

This discussion thread is closed

Replies have been disabled for this discussion.