I've got a PHP page that contains a javascript that gets values from a few different form elements, and uses them to form a MySql update statement. The page gets submitted to itself and PHP gets the update statement via $_POST[] inside stripslashes() and sends the query to the db, either setting the result to a variable or die, printing mysql_error()

The javascript works fine, throws no errors. The PHP works fine, throws no errors, except-

the resulting single update statement works: UPDATE data SET title = 'foo' WHERE id = '50';

but a multi update statement fails & returns a MySql syntax error: UPDATE data SET title = 'foo' WHERE id = '50'; UPDATE data SET title = 'bar' WHERE id = '51';

When I echo the multi update statement to the browser and manually copy & paste it into a shell session the statement works fine and throws no syntax errors.

Any ideas of what's going wrong?

The scripts are below for reference:
** bulletin board code is stripping forward slashes that exist in JS- the following line has forward slashes:
updateStatement += 'UPDATE data SET ' + dataToUpdate + ' = '' + newData + '' WHERE id = '' + imageToUpdate + '';';

<script type='text/javascript' language='Javascript'>
<!--

// the 'displayedImages' form contains 10 images each with a checkbox 
// that contains the image's MySql id as its value
displayedImages = document.forms['displayedImages'].elements;

// the 'updateData' form contains a select list of MySql column names, 
// a text input for inputting a new value and a hidden input to set a MySql query to
updateData = document.forms['updateData'].elements;

// get the MySql column name from the select list
dataToUpdate = updateData.elements['dataList'].value;

// get the value of new data from the text input
newData = updateData.elements['newData'].value;

// declare var to contain MySql query
updateStatement = '';

for (i=0;i<displayedImages.length;i++) {
	if (displayedImages[i].type == 'checkbox' && displayedImages[i].checked == true) {
		// get an image id from a checkbox
		imageToUpdate = displayedImages[i].name;
		// build the MySql query
		updateStatement += 'UPDATE data SET ' + dataToUpdate + ' = \'' + newData + '\' WHERE id = \'' + imageToUpdate + '\';';
	}
}

// set 'updateStatement' value to a hidden input 'mysqlQuery'
updateData.elements['mysqlQuery'].value = updateStatement;

// submit the 'updateData' form
updateData.submit();

// -->
</script>

<?php
function updateData() {
	$this->update = stripslashes($_POST['mysqlQuery']);
	$this->result = mysql_query($this->update) or die('Query failed: ' . mysql_error());
	//echo $this->update;
}
?>

    Hi,

    what is the reason that you use javascript to build the queries ?
    Submitting queries in a form is not a very secure way (SQL injection). I'd suggest to change your app to do all the stuff in PHP.

    How does the syntax error look like (the part 'near ....').

    Thomas

      Thanks for your reply Thomas-

      this thing's got me completely baffled...

      Originally posted by tsinka
      Hi,

      what is the reason that you use javascript to build the queries ?
      Submitting queries in a form is not a very secure way (SQL injection). I'd suggest to change your app to do all the stuff in PHP.

      I'm using a combo of PHP & JS for a number of things on this page, namely to populate 2 select lists- the first gets populated by PHP on page load, then on change, JS populates the second- eliminates the need for multiple DB queries. The page is a homegrown table update interface that's not public and is password protected.


      How does the syntax error look like (the part 'near ....').

      Thomas

      Query failed: You have an error in your SQL syntax near '; UPDATE data SET title = 'bar' WHERE id = '51'; ' at line 1

      -Danny

        Hi,

        just a test ... try this function:

        <?php 
function updateData() { 
    $this->update = stripslashes($_POST['mysqlQuery']);
    $arrQueries = explode(";",$this->update);

for ($i=0;$i<count($arrQueries);$i+) {
$this->result = mysql_query($arrQueries[$i]) or die('Query failed: ' . mysql_error()); 
}
//echo $this->update; 
} 
?>

        Does this work ?

        Alternatively change the javascript line

        updateStatement += 'UPDATE data SET ' + dataToUpdate + ' = '' + newData + '' WHERE id = '' + imageToUpdate + '';';

        to

        updateStatement += 'UPDATE data SET ' + dataToUpdate + ' = \'' + newData + '\' WHERE id = \'' + imageToUpdate + '\'; ';

        I'm not really sure if the backslashes are really missing in your post. If you want to post backslashes in PHPBuilder you have to type them twice.

        Thomas

          thanks again Thomas, yeah, the backslashes are there. I didn't realize I had to escape 'em in the messageboard...

            Does it work using the for loop ?

            Thomas

              Yeah the for loop works too.

              I think I found the problem- it seems to be a limitation in PHP (never thought I'd hit THAT wall...)

              Another case of RTFM...

              Apparently multi statement queries are not possible in PHP-

              "Note: The query string should not end with a semicolon."

              [MAN]mysql_query[/MAN]

                Write a Reply...