Applying an SQL query through a for...while loop (!!)

[Steve Macleod]

Hi there,

Newbie at this, so here goes:

I am attempting to build a page that updates multiple records. The page uses
as its input, a series of dynamically generated text boxes from the previous
page (through POST variables)

The text boxes on the previous page are named as follows:

txt_1
txt_2
txt_3
etc....

txt_ans1
txt_ans2
txt_ans3
etc...

with a hidden field next to each box to identify the primary key as follows:

txt_hidden_1
txt_hidden_2
txt_hidden_3

etc...

They are dynamically generated as they are read from the database, hence the
common naming conventions.

My form handling page (which the form POST action points to) is designed to
cycle through each of these values and update the recordset WHERE
id=txt_hidden_$counter. If this is unclear (probally is) then look at the
code below:
$number_of_records = $_POST[txt_num_rows]; //get the value from the hidden
field which contains the total number of records
//create the sql statement. This should look like UPDATE.... WHERE
id=somevariable
//the counter will increment for each so we need to have the counter
incremented for each time the query is run
//need to loop through all the records and apply the SQL query

for ( $marker=1; $marker<=$number_of_records; $marker++) {

$sql = "UPDATE faq SET faq_quest='$_POST[txt_$marker]',
faq_ans='$_POST[txt_ans$marker]' WHERE faq_id = '$_POST[txt_ans$marker]'";
//code here to actually RUN the SQL query
}

As you can probally guess from the fact that I am typing this massive post,
the above code does not work. I keep getting errors when I attempt to use
the $marker variable from within the SQL query, to cycle through all the
POST variables and update all the records. I think its to do with the single
quotes ', which tell the compiler not to process variables, but rather take
everything literally, so-to-speak.

I guess, my question is: how can I re-write the above snippet to allow me to
do what I wish to achieve.

Many, many thanks to anyone who takes the time to read this and reply.

[David Mackenzie]

On Wed, 4 Feb 2004 03:46:44 -0000, "Steve Macleod"
<st********@leodhotmail.com> wrote:

//need to loop through all the records and apply the SQL query

for ( $marker=1; $marker<=$number_of_records; $marker++) {

$sql = "UPDATE faq SET faq_quest='$_POST[txt_$marker]',
faq_ans='$_POST[txt_ans$marker]' WHERE faq_id = '$_POST[txt_ans$marker]'";
//code here to actually RUN the SQL query
}

As you can probally guess from the fact that I am typing this massive post,
the above code does not work. I keep getting errors when I attempt to use
the $marker variable from within the SQL query, to cycle through all the
POST variables and update all the records. I think its to do with the single
quotes ', which tell the compiler not to process variables, but rather take
everything literally, so-to-speak.

IMO, the worst thing that PHP does is this parsing of variables within
string literals as it can lead to confusion.

Anyway, to answer your query, use the concatenation operator "."

$sql = "UPDATE faq SET faq_quest='".$_POST["txt_".$marker]."',
faq_ans='".$_POST["txt_ans".$marker]."' WHERE faq_id =
'".$_POST[txt_ans".$marker]."'";

http://www.php.net/manual/en/languag...ors.string.php

I may have got one in the wrong place somewhere, but you get the
general idea.

I've just noticed you have single quotes around your primary key. Is
your primary key a varchar? If not, the single quotes are not needed.

--
David ( @priz.co.uk )

[Steve Macleod]

Thanks for that. Thats really helped me on my way. My understanding of php
is at the moment, basic, but in going to get there eventually!

Cheers again!
"David Mackenzie" <[e-mail]> wrote in message
news:00********************************@4ax.com...

On Wed, 4 Feb 2004 03:46:44 -0000, "Steve Macleod"
<st********@leodhotmail.com> wrote:

//need to loop through all the records and apply the SQL query

for ( $marker=1; $marker<=$number_of_records; $marker++) {

$sql = "UPDATE faq SET faq_quest='$_POST[txt_$marker]',
faq_ans='$_POST[txt_ans$marker]' WHERE faq_id = '$_POST[txt_ans$marker]'";//code here to actually RUN the SQL query
}

As you can probally guess from the fact that I am typing this massive post,the above code does not work. I keep getting errors when I attempt to use
the $marker variable from within the SQL query, to cycle through all the
POST variables and update all the records. I think its to do with the singlequotes ', which tell the compiler not to process variables, but rather takeeverything literally, so-to-speak.

IMO, the worst thing that PHP does is this parsing of variables within
string literals as it can lead to confusion.

Anyway, to answer your query, use the concatenation operator "."

$sql = "UPDATE faq SET faq_quest='".$_POST["txt_".$marker]."',
faq_ans='".$_POST["txt_ans".$marker]."' WHERE faq_id =
'".$_POST[txt_ans".$marker]."'";

http://www.php.net/manual/en/languag...ors.string.php

I may have got one in the wrong place somewhere, but you get the
general idea.

I've just noticed you have single quotes around your primary key. Is
your primary key a varchar? If not, the single quotes are not needed.

--
David ( @priz.co.uk )

[David Precious]

Steve Macleod wrote:

<snip>

for ( $marker=1; $marker<=$number_of_records; $marker++) {

$sql = "UPDATE faq SET faq_quest='$_POST[txt_$marker]',
faq_ans='$_POST[txt_ans$marker]' WHERE faq_id = '$_POST[txt_ans$marker]'";
//code here to actually RUN the SQL query
}

This code is insecure! You're leaving yourself open to an SQL injection
attack.

Do you check what $_POST[txt_1] etc contain first? If not, you could be
letting yourself in for a whole world of trouble.

Say I post the param txt_1 to the script (along with the other params needed
to fool it into running etc).

Imagine that I set the value of txt_1 to:

"'; DROP DATABASE mysql; -- "

(the double quotes indicate the start and end, the single quote is part of
the value).

That means you're running two queries, and if the attack is carried out
right, the second query ('injected' into the SQL) could do some damage. If
your setup is anywhere near secure then the MySQL user you're connected to
the DB as would not have the right to drop the 'mysql' database, but it's
an example of what could happen.

The basic rule I'd suggest is to always call addslashes() on EVERY value
that you're going to use in an SQL query. A regular expression can also be
used to remove dodgy characters, or even more secure, remove anything
that's NOT one of the characters you want to allow.
Cheers

Dave P

--
David Precious
http://www.preshweb.co.uk/