What is up with implode?

Tezread

I am doing a conditional update of a database table ut it is not happening- can anyone see any errors in my syntax?

	$sql = "SELECT * FROM qualityuser WHERE username = '".$username."'"; 
	$result = mysql_query($sql); 
// if we have rows, fetch them & prepopulate the form
//check if there and update/insert
if(mysql_num_rows($result) > 0) { 

$sql = 'UPDATE qualityuser SET ' . implode(',', $_SESSION['formdata1']) . ' WHERE username = ' . $_SESSION['username']; 
foreach ($_SESSION['formdata1'] as $col => $val) { 
if ($col <> 'submit'){ 
        $sql .=", $col = '$val' "; 
} 
} 


mysql_query($sql, $connection) or die(mysql_error()); 

}

else  {


$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
foreach ($_SESSION['formdata1'] as $col =>$val ){
if($col <> 'submit'){
	$sql .=", $col = '$val' ";
}

}
mysql_query($sql, $connection) or die(mysql_error());

the error message is simply:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'not selected,Donald,Duck,Disney Co,111,222,333,444,555,1,1,0,0,

stolzyboy

echo out your $sql variable and post the whole thing... there is likely a quoting issue there somewhere

stolzyboy

leatherback

This: implode(',', $_SESSION['formdata1']) results in fields separated by comma, but text is not enclosed in quotes, which might be giving you some trouble.

laserlight

Yes, there is at least one quoting problem, your indentation style needs work, and you might want to use COUNT(*) to find number of rows.

Tezread

this is all of it basically. Not sure where how I can echo $sql if no results are coming up- this debugging approach has always confused me. How can you echo the result of something that cannot be echoed in the first place?

<?php 
$_SESSION['formdata1']=$_POST;
include("connect.php"); 
//array to hold form errors       

$form_error = array(); 
//validate your form data 
if(!isset($_POST['firstname'])){ 
$form_error[] = "No first name entered"; 
} 
if(!isset($_POST['secondname'])){ 
	$form_error[] = "No second name entered";       

	}       

if(count($form_error) > 0){ 
//hold the error in session so you can display the error in your form page. 
	$_SESSION['form_error'] = $form_error; 
//redirect to the form 
	header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1a_v1.php");       

}  

else { 
	$username = $_SESSION['username'];
	$sql = "SELECT * FROM qualityuser WHERE username = '".$username."'"; 
	$result = mysql_query($sql); 
// if we have rows, fetch them & prepopulate the form
//check if there and update/insert
if(mysql_num_rows($result) > 0) { 

$sql = 'UPDATE qualityuser SET ' . implode(',', $_SESSION['formdata1']) . ' WHERE username = ' . $_SESSION['username']; 
foreach ($_SESSION['formdata1'] as $col => $val) { 
if ($col <> 'submit'){ 
        $sql .=", $col = '$val' "; 
} 
} 


mysql_query($sql, $connection) or die(mysql_error()); 
echo $sql;
	}

else  {


$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
foreach ($_SESSION['formdata1'] as $col =>$val ){
if($col <> 'submit'){
	$sql .=", $col = '$val' ";
}

}

mysql_query($sql, $connection) or die(mysql_error()); 
echo $sql;

leatherback

You do not echo the result, but the content of the variable $sql, right before you send it to the database.

This will give you insight in the query that is submitted to the database, let's you test it (in e.g., phpmyadmin) and manually adjust it. once you get it to work in the database interface, uyou can adjust your php code to reflect the changes required in the sql;

so reverse:
mysql_query($sql, $connection) or die(mysql_error());
end
echo $sql;

to get:

echo $sql; 
// you might want to exit() here
mysql_query($sql, $connection) or die(mysql_error());

Tezread

I see-

here is my debug now:

<?php 
include("include/session.php");
$_SESSION['formdata1']=$_POST;
include("connect.php"); 
//array to hold form errors       

$form_error = array(); 
//validate your form data 
if(!isset($_POST['firstname'])){ 
$form_error[] = "No first name entered"; 
} 
if(!isset($_POST['secondname'])){ 
	$form_error[] = "No second name entered";       

	}       

if(count($form_error) > 0){ 
//hold the error in session so you can display the error in your form page. 
	$_SESSION['form_error'] = $form_error; 
//redirect to the form 
	header("Location: http://www.mindseyemidlands.co.uk/notts_quality/info_resource/selfassess_part1a_v1.php");       

}  

else { 
	$username = $_SESSION['username'];
	$sql = "SELECT * FROM qualityuser WHERE username = '".$username."'"; 
	$result = mysql_query($sql); 
// if we have rows, fetch them & prepopulate the form
//check if there and update/insert
if(mysql_num_rows($result) > 0) { 

$sqlupdate = 'UPDATE qualityuser SET ' . implode(',', $_SESSION['formdata1']) . ' WHERE username = ' . $_SESSION['username']; 
foreach ($_SESSION['formdata1'] as $col => $val) { 
if ($col <> 'submit'){ 
        $sqlupdate .=", $col = '$val' "; 
} 
} 

echo $sqlupdate;
	mysql_query($sql, $connection) or die(mysql_error()); 

}

else  {


$sqlinsert = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
foreach ($_SESSION['formdata1'] as $col =>$val ){
if($col <> 'submit'){
	$sqlinsert .=", $col = '$val' ";
}

}
echo $sqlinsert;	
	mysql_query($sql, $connection) or die(mysql_error());

and the results:

UPDATE qualityuser SET donald,not selected,Donald,Duck,Disney Co,111,222,333,444,555,1,1,0,0,0,1,1,0,0,0,5 or less,5 or less,5 or less,1,1,0,0,0,1,1,0,0,0,0,0,0,1,1,0,0,0,0,0,submit WHERE username = donald, username = 'donald' , title = 'not selected' , firstname = 'Donald' , secondname = 'Duck' , organisation = 'Disney Co' , telephone = '111' , address1 = '222' , address2 = '333' , town = '444' , postcode = '555' , registeredcharity = '1' , incorporated = '1' , regionalassociation = '0' , regionalcharity = '0' , project = '0' , nature_selfhelp = '1' , nature_frontline = '1' , nature_intermediary = '0' , nature_infrastructure = '0' , nature_partnership = '0' , involved_commitee = '5 or less' , involved_volunteers = '5 or less' , involved_paid = '5 or less' , funded_unrestrictedfunds = '1' , funded_localcouncil = '1' , funded_undercontract = '0' , funded_grants_fees = '0' , funded_socialenterprise = '0' , activity_care = '1' , activity_advice_individual = '1' , activity_advice_organisations = '0' , activity_specialist = '0' , activity_community = '0' , activity_awareness = '0' , activity_sport = '0' , activity_mutual_support = '0' , have_policies_procedures = '1' , have_management_structure = '1' , have_communication = '0' , have_supervision = '0' , have_strategy = '0' , have_aims = '0' , have_review = '0' You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'not selected,Donald,Duck,Disney Co,111,222,333,444,555,1,1,0,0,

Tezread

leatherback wrote:
This: implode(',', $_SESSION['formdata1']) results in fields separated by comma, but text is not enclosed in quotes, which might be giving you some trouble.

I think I know what you mean but cannot translate it into the syntax.

leatherback

Did you write this script?

There is a serious problem..

Here you just create an sql-inject waiting to happen, by using the form data without checking it directly into your sql, you open your website for hackers, and put a "welcome" sign on the door: ($sql = 'UPDATE qualityuser SET ' . implode(',', $SESSION['formdata1']) . ' WHERE username = ' . $SESSION['username']; )

I think the $sql looks completely different from what you expect. Check that first. Somehow I feel that the query is complete nonsense at the moment. Do what was suggested before: Echo $sql out, and if you do not understand what it shows, post it here.

Tezread

I have stradily built this script using parts of archives as well as editing stuff found on this site.

Basically I have a form with fields in it

<?php
include("include/session.php");
$_SESSION['formdata1']='';
$username = $_SESSION['username'];
include("connect.php");
// query 
$sql = "SELECT * FROM qualityuser WHERE username = '".$username."'"; 
$result = mysql_query($sql); 
// if we have rows, fetch them & prepopulate the form
if(mysql_num_rows($result) > 0) { 
   $row = mysql_fetch_array($result); 
} else { 
   $row = array('firstname'=>'','secondname'=>'','organisation'=>'','telephone'=>'','address1'=>'','address2'=>'','town'=>''
   ,'postcode'=>'' ,'registeredcharity'=>'','incorporated'=>'','regionalassociation'=>'','regionalcharity'=>'','project'=>''
   ,'nature_selfhelp'=>'','nature_frontline'=>'','nature_intermediary'=>'','nature_infrastructure'=>'','nature_partnership'=>''
   ,'involved_commitee'=>'','involved_volunteers'=>'','involved_paid'=>'','funded_unrestrictedfunds'=>'','funded_localcouncil'=>''
   ,'funded_undercontract'=>'','funded_grants_fees'=>'','funded_socialenterprise'=>'','activity_care'=>'','activity_advice_individual'=>''
   ,'activity_advice_organisations'=>'','activity_specialist'=>'','activity_community'=>'','activity_awareness'=>'','activity_sport'=>''
   ,'activity_mutual_support'=>'','have_policies_procedures'=>'','have_management_structure'=>'','have_communication'=>'','have_supervision'=>''
   ,'have_strategy'=>'','have_aims'=>'','have_review'=>'');
} 
?> 
<strong>About you
                          <input type="hidden" name="username" value="<?=$_SESSION['username']?>">             

                      </strong></td>
                    </tr>

                <tr>
                  <td>&nbsp;</td>
                  <td>Title</td>
                  <td colspan="3"><select name="title" size="1" id="title">
                      <option selected>not selected</option>
                      <option>Mr.</option>
                      <option>Mrs.</option>
                      <option>Miss</option>
                      <option>Ms.</option>
                  </select></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>First name </td>
                  <td colspan="3"><input name="firstname" type="text" id="firstname" value="<?=$row['firstname']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Second name </td>
                  <td colspan="3"><input name="secondname" type="text" id="secondname" value="<?=$row['secondname']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Organisation</td>
                  <td colspan="3"><input name="organisation" type="text" id="organisation" value="<?=$row['organisation']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Telephone</td>
                  <td colspan="3"><input name="telephone" type="text" id="telephone3" value="<?=$row['telephone']?>"></td>
                </tr>

                <tr>
                  <td>&nbsp;</td>
                  <td>Address1</td>
                  <td colspan="3"><input name="address1" type="text" id="address1" value="<?=$row['address1']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Address2</td>
                  <td colspan="3"><input name="address2" type="text" id="address2" value="<?=$row['address2']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Town</td>
                  <td colspan="3"><input name="town" type="text" id="town" value="<?=$row['town']?>" size="40"></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>Postcode</td>
                  <td colspan="3"><p>
                    <input name="postcode" type="text" id="postcode" value="<?=$row['postcode']?>" size="20">
                  </p>
                    </td>
                </tr>

                <tr>
                  <td>&nbsp;</td>
                  <td colspan="4"><strong>Section 1<br>
                    What type of organisation are you? (Tick as many as apply)</strong></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td colspan="3">&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td colspan="3"><p>Registered charity</p></td>
                  <td><div align="center">
				  <input type="hidden" name="registeredcharity" value="0">
                      <input name="registeredcharity" type="checkbox" value="1" <?php if ($row['registeredcharity'] == 1) {echo 'checked';} ?>/> 




                  </div></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td colspan="3"><p>Incorporated</p></td>
                  <td><div align="center">
				  <input type="hidden" name="incorporated" value="0">
                    <input name="incorporated" type="checkbox" id="incorporated" value="1" <?php if ($row['incorporated'] == 1) {echo 'checked';} ?>/>
                  </div></td>
                </tr>
                <tr>

etc

these fields are placed in an array $_SESSION['formdata1'] and then in the formhandler I test to see if it has already been entered. If it has then an update is carried out- if not an insert is made. I know this is lame but I cannot do any more with this script as my experience is only relatively low. This is probably why it is in such a mess

Tezread

here is the $sqlupdate echoed - I do not see how to respond to this

Tezread

replies coming up now. Here is $sqlupdate echoed - i do not get this.
I have based my update approach on my insert approach which works a treat:

UPDATE qualityuser SET test220107v1,not selected,Moke,Karron,Manco,03222 33666,laalla,dududu,papapap,apapap,0,1,1,0,0,0,0,0,0,1,6 to 10,26 to 50,26 to 50,1,1,0,0,0,0,0,0,0,0,0,1,1,0,1,1,0,0,0,0,submit WHERE username = test220107v1, username = 'test220107v1' , title = 'not selected' , firstname = 'Moke' , secondname = 'Karron' , organisation = 'Manco' , telephone = '03222 33666' , address1 = 'laalla' , address2 = 'dududu' , town = 'papapap' , postcode = 'apapap' , registeredcharity = '0' , incorporated = '1' , regionalassociation = '1' , regionalcharity = '0' , project = '0' , nature_selfhelp = '0' , nature_frontline = '0' , nature_intermediary = '0' , nature_infrastructure = '0' , nature_partnership = '1' , involved_commitee = '6 to 10' , involved_volunteers = '26 to 50' , involved_paid = '26 to 50' , funded_unrestrictedfunds = '1' , funded_localcouncil = '1' , funded_undercontract = '0' , funded_grants_fees = '0' , funded_socialenterprise = '0' , activity_care = '0' , activity_advice_individual = '0' , activity_advice_organisations = '0' , activity_specialist = '0' , activity_community = '0' , activity_awareness = '0' , activity_sport = '1' , activity_mutual_support = '1' , have_policies_procedures = '0' , have_management_structure = '1' , have_communication = '1' , have_supervision = '0' , have_strategy = '0' , have_aims = '0' , have_review = '0'

leatherback

You might want to have a look at the update syntax in the manual (http://mysql.org/doc/refman/5.1/en/update.html) which is seriously different from what you are trying.

Furthermore: All text fields requiree quotes around the values you try to insert/update.

You want:
update <TABLE> set <Col> = <Value>, <Col2> = <Value2>,... WHERE ... Limit ...

So this:
SET test220107v1,not selected,Moke,Karron,Manco,03222 33666,laalla,dududu,papapap,apapap,0,1,1,0,0,0,0,0,0,1,6 to 10,26 to 50,26 to 50,1,1,0,0,0,0,0,0,0,0,0,1,1,0,1,1,0,0,0,0,submit

Does not work.

And as mentioned before: Just bluntly merging your POST array into the query is a serious risk, and in your case it just does not work, as things like the SUBMIT come into play.

Tezread

leatherback wrote:
You might want to have a look at the update syntax in the manual (http://mysql.org/doc/refman/5.1/en/update.html) which is seriously different from what you are trying.

Furthermore: All text fields requiree quotes around the values you try to insert/update.

You want:
update <TABLE> set <Col> = <Value>, <Col2> = <Value2>,... WHERE ... Limit ...

QUOTE]

Updating this way is very easy as I have used this approach so many times before. The reason I am using $SESSION['formdata1'] as an array to store all the fields in the form is because one day I might have over 50 fields in a form and using this approach:
$answer[1] = $_POST['time']; 
$answer[2] = $_POST['demand']; 
$answer[3] = $_POST['external_support']; 
$answer[4] = $_POST['training']; 
$answer[5] = $_POST['complexity']; 
$answer[6] = $_POST['depth']; 
$answer[7] = $_POST['partuse']; 
$answer[8] = $_POST['cost']; 
$answer[9] = $_POST['orgwhole']; 
$answer[10] = $_POST['external_validated']; 
$sql = "UPDATE qualityuser SET time = '$answer[1]',
demand = '$answer[2]',
external_support = '$answer[3]',
training = '$answer[4]',
complexity = '$answer[5]',
depth = '$answer[6]',
partuse = '$answer[7]',
cost = '$answer[8]',
orgwhole = '$answer[9]',
external_validated = '$answer[10]'

WHERE username = '$username'";
produces extra lines of code when I know it can be done more concisely

Take this insert procedure:
$sqlinsert = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
	foreach ($_SESSION['formdata1'] as $col =>$val ){
	if($col <> 'submit'){
		$sqlinsert .=", $col = '$val' ";
this is basically saying for every field in the session var 'formdata' insert the value that matches up with that field in the database.
In terms of security- I will look into this but for now I am looking for consise procedures.

I have been trying for weeks now to build a dynamic and short coded method for an UPDATE that is similar in construction to the INSERT and bases itself on a session var.

leatherback

OK,

My last post in this thread. If you decide to ignore advice, please do so, but on your own.

I have never said you could not automate things, but do it sensibly. You have no filtering whatsoever on your userinput. Whatr happens if someone decides to spoof your form, and submit their own data? Or even easier.. Uses an sql string to hack your database.

Have a look at http://en.wikipedia.org/wiki/SQL_injection, and have a browse through these forums. PhP has adjusted the default setting for global variables from on to off, so that peopl cannot easily abuse scripts anymore. Your code open the front door.

Tezread

I do take our advice on board and it looks like I will have to stick to the basic approach coding in spite of mine and others best efforts. Will read up on the Wiki link you posted too.

Due respect for your input leatherback

leatherback

Ok, as a lst hint:

Create a function that will:

-reads an array with permitted fields, possible columname in the database, and their data type: Integer / String (You define this once for your form, and can include this where-ever you need it)
-Takes the POSTed data array, loops though it, and for each permitted value it:
- Tests datatype, and adds slashed / mysql_escape_real where required
- creates the proper 'Colum = 'value', combination, and appends it to the string
- Returns this string to the main program

You can just call this function to create your SQL, controlling user input, yet making your coding cleaner.

You could even make the function test whether certain fields are empty, by also passing a required_fields array to it.

Tezread

Whooah! this is some job for me. I did soem more research on the archives and found something which will get the ball rolling but I don't think I have the skills/logic to come up with a perfect solution.

Here is what I found:

<?php
include("include/session.php");
$sql = 0;
$sql1 = 0;
$sql2 = 0;
$sql3 = 0;
$table = "qualityuser";
$username = $_SESSION['username'];
include("connect.php"); 
$sql1 = "UPDATE $table SET ";
$i=0; 
foreach($HTTP_GET_VARS as $key=>$val) { 
	$fieldname[$i] = $key;
	$values[$i] = $val;
	$sql2 .= $fieldname[$i] . "=" . "'" . $values[$i] . "'" . ",";
	$sql3 = substr($sql2,0,-1);
	$i++; 
	}//----end foreach
	$sql = $sql1 . $sql3 . " WHERE username='$username'";

echo $sq11;
echo $sq12;
echo $sq13;

mysql_query($sql, $connection) or die(mysql_error()); 
?>

I fear this does fall foul of possible SQL injections as I am using WHERE $username...

Ther above results in undefined variable error messages for $sql1, $sql2 and $sql3.

I really appreciate your patience leatherback- you don't have to help anymore. If I get stuck I will contract this out as I have been trying it for so long.