[RESOLVED] Issue someplace with syntax...

IceRegent

Hello. I have a set of pages that work in another module. I copied the code over to a new module, and made changes to the user variables, and, the code works in the other module, but it gives me the following error message when I try it in the new module:

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 'WHERE bid='3'' at line 1

This error comes in the second script, after the submit button is pressed in the first script, and the processing begins in the second script.

Here are the two code pages:

bio_update.php

<?php
echo 'Hello and welcome <b>' . $_SESSION['YourName'] . '</b> !!';
$whois = $_SESSION['YourName'];
$result = mysql_query("SELECT bid , uname , ubio , pic FROM biography WHERE uname LIKE '$whois'") or exit(mysql_error());
$bioarray = mysql_fetch_assoc($result);
foreach ($bioarray as $key => $value)
{
    $$key = htmlspecialchars($value);
}

echo 'Make your <B>CHANGES</b> in the form below:<br><br>
<form enctype="multipart/form-data" method="post" action="bio_change_update.php">
<table width="100%">
<tr><td><b>Upload a picture?</b></td></tr>
<tr><td><input type="file" name="biospic"><input type="submit" name="submit" value="UPDATE!"></td></tr>
<tr><td><input type="hidden" name="biosid" value="' . $bid . '"</td></tr>
<tr><td><b>About You:</b></td></tr>
<tr><td><textarea name="biodesc" rows="20" cols="80"> ' . $ubio . '</textarea></td></tr>
</table>
</form>
';

?>

bio_change_update.php

<?php
session_start();
include 'config.php';
$picture = basename( $_FILES['biospic']['name']);
//Correctly escape out all info in fields from POST operation
foreach ($_POST as $key => $value)
	{
    $value = trim($value);
    if ($value != '') {$value = "'" . mysql_real_escape_string($value) . "'";}
     else {$value = 'NULL';}
    $$key = $value;
	}
$sql = "UPDATE biography SET ubio=$biodesc, pic=$biospic WHERE bid=$biosid";
$result = mysql_query($sql) or exit(mysql_error());
//Now we need to upload the file
if ($picture <> NULL) {
$target_path = "biography/";
$target_path = $target_path . basename( $_FILES['biospic']['name']);

if(move_uploaded_file($_FILES['biospic']['tmp_name'], $target_path)) {
    echo "The file ".  basename( $_FILES['biospic']['name']). 
    " has been uploaded";
	} else{
    echo "There was an error uploading the file, please try again!";
    echo 'Here is some more debugging info:';
				print_r($_FILES);
				print "</pre>";
	}
}
mysql_close;
$_SESSION['admin'] = NULL;
$_SESSION['nametest'] = basename( $_FILES['biospic']['name']);
include 'index.php';
?>

I dont see what is causing the error. I am not sure exactly what the error message is pointing at. Any one see what is wrong?

Thanks;
Ice

bradgrafelman

String values in SQL must be surrounded in quotes, just like PHP.

IceRegent

Well, as I stated in the previous post, the SAME code works in another module. Same code exactly, copied over. So then, tell me why, that this one works in the other module and doesnt in this one? And yes, I did try it with and without quotes, even tho it works in the other module without. Here, let me post the code of the other module that does work:

<?php
session_start();
include 'config.php';
//Load up the array with the $_POST variables
foreach ($_POST as $key => $value)
{
    $value = trim($value);
    if ($value != '') {$value = "'" . mysql_real_escape_string($value) . "'";}
     else {$value = 'NULL';}
    $$key = $value;
}
$sql = "UPDATE news SET title=$newstitle, news=$newsart WHERE nid=$newsid";
$result = mysql_query($sql) or exit(mysql_error());
echo 'UPDATE OK'; 
mysql_close;
include 'index.php';
?>

Now, that code works without any problems. So, maybe there is a syntax error somehow that crept in when I wasnt looking?

Ice

bradgrafelman

Try changing this:

$result = mysql_query($sql) or exit(mysql_error());

to this:

$result = mysql_query($sql) or exit("Query: $sql<hr>" . mysql_error());

and paste the new error information it outputs (e.g. including the exact SQL query that was sent).

EDIT: Oops, I didn't notice the single quotes getting added in the foreach() function. Still make the above changes, however - it'll at least give us more information about the problem.

Also note that your foreach() loop is basically imitating register_globals, a directive which has long since been declared a security risk: [man]security.globals[/man].

IceRegent

Hello brad. I will make the changes for the debuggin info as you suggest. However, the specific for-each loop code came from a suggestion from devinemke at the thread : http://phpbuilder.com/board/showthread.php?t=10365436. I was working with a code set and that is what he suggested, which worked very well for what I was needing to do. I do not claim to be extremely knowledgeable in coding, so I very much appreciate the help that folks give to me in guidance. But, as I said,the second set of code that I pasted in from the module that does work, uses the same code as the module that doesnt work. I couldnt find any differences in the formatting of the codes, so, hopefully the debugging info will give some clue. Let me put that in real quick and see what it say, and I will get back to you.

Thanks;
Ice

IceRegent

Ok, here is the result of that info in the code:

Query: UPDATE biography SET ubio='Craig - Plays thru a Crate BT220 Combo Amp, with a mere 220 Watts of power. Does it work now?', pic= WHERE bid='3'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 'WHERE bid='3'' at line 1

I am not sure exactly what that is saying to me. Does it mean anything to you?

Ice

IceRegent

Ok, that is odd. I made a minor change, added some single quotes, and it seems to work. With and without a picture being selected. Here is where the change was made. Can you help me understand why making the single change would make a difference?

$sql = "UPDATE biography SET ubio=$biodesc, pic='$picture' WHERE bid=$biosid";

Notice I put single quotes around the variable $picture, and only around that. And that makes it work. I do not understand that. That variable comes from the basename derivation of the selected file in the files field. There must be something in that that is causing that.

Ice

bradgrafelman

If you'll notice from your post above, your problem is that $picture was neither a quoted string value nor the string 'NULL' - instead, it actually was NULL. I'm willing to bet that if you turn on display_errors and set error_reporting to E_ALL, you'll actually find out that the $picture variable doesn't exist.

In your foreach() code above, if an item in the $_POST array (e.g. 'picture') doesn't exist, there will be no corresponding variable $picture created for it. This is one of a few reasons why I cautioned against using the foreach() loop code. At this point, I think you should get rid of it and learn how to handle incoming data properly.

What I would do is something like this:

$biodesc = (isset($_POST['biodesc'] ? "'" . mysql_real_escape_string($_POST['biodesc']) . "'" : 'NULL');
// etc. for the other incoming variables you use