CODEWITHSUNDEEP
mysqldatabasearraysinsert
Isaac Camargo
Isaac Camargo

Reputation: 13

Trouble Inserting An Array of Information into a MySQL Database

I am having an issue with inserting an array of information into a mysql database. Basically I built a sortable gallery similar to Facebook's photo albums that can be arranged by moving the div to a new spot with jquery's sortable function.

I am using Ajax to call a php file which will inser the new order of the div's into the DB. The information is being passed correctly, it is just not being inserted correctly.

The error I am receiving is:
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 'Array' at line 1

The Php code is: 

foreach ($_GET['listItem'] as $position => $item) {
    if ($item >= 1) {
        $sql[] = "UPDATE table SET order = '{$position}' WHERE id = '{$item}'";
        mysql_query($sql) or die(mysql_error());
    }
}

If I remove the mysql_query function and just do a print_r, I get:

Array
(
    [0] => UPDATE table SET order = '0' WHERE id = '2'
    [1] => UPDATE table SET order = '1' WHERE id = '4'
    [2] => UPDATE table SET order = '2' WHERE id = '3'
    [3] => UPDATE table SET order = '3' WHERE id = '1'
    [4] => UPDATE table SET order = '4' WHERE id = '5'
    [5] => UPDATE table SET order = '5' WHERE id = '6'
)

This is the first time I have tried to do something like this. Any help would be great.

Thank you in advance for the help!

Upvotes: 0

Views: 429

Answers (2)

futureal
futureal

Reputation: 3045

Sounds like there is some confusion about what the [] operator does. You use [] when you want to append an element to the end of an existing array.

For example:

 $sql = array();
 $sql[] = 'UPDATE table SET order = "0" WHERE id = "2"';
 mysql_query($sql); // this will produce the error you are seeing

Versus:

 $sql = 'UPDATE table SET order = "0" WHERE id = "2"';
 mysql_query($sql); // this will work

You should rewrite your code as such:

foreach ($_GET['listItem'] as $position => $item) {
  if ($item >= 1) {
    $sql = "UPDATE table SET order = '{$position}' WHERE id = '{$item}'";
    mysql_query($sql) or die(mysql_error());
  }
}

That will do what you are intending. However, this is still not a good idea, since you are passing untrusted $_GET data directly to the database. I could, for example, call your script with a string like:

http://yoursite.com/yourscript.php?listItem=1'%3B%20DROP%20TABLE%20yourtable%3B

Since the value of listItem is going directly to the database -- and the $item >= 1 check is insufficient, since PHP will evaluate a string as an integer if it begins with numeric data -- all I have to do is add a single quote to terminate the previous query, and I am then free to inject whatever SQL command I'd like; this is a basic SQL injection attack. Whenever you write database-touching code, you should cleanse any input that might be going to the database. A final version of your code might look like:

foreach ($_GET['listItem'] as $position => $item) {

  if ($item >= 1) { // this check may or may not be needed depending on its purpose

    $sql = 'UPDATE table SET order = "' . mysql_real_escape_string($position) . '" WHERE id = "' . mysql_real_escape_string($item) . '"';
    mysql_query($sql) or die(mysql_error());

  }

}

There are other ways to cleanse input data as well, that is just one of them. Hope that helps.

Upvotes: 0

Austin Haskew
Austin Haskew

Reputation: 354

In mysql_query($sql) $sql is an array, therefore it's value is simply Array. When you assign $sql[] = "UPDATE table SET order = '{$position}' WHERE id = '{$item}'"; simply make this line $sql = "UPDATE table SET order = '{$position}' WHERE id = '{$item}'";. That should solve your problem.

EDIT:

You can leave the [] and simply remove the mysql_query from where it is. After your foreach list item, add this:

foreach($sql as $query) {
    mysql_query($query);
}

Upvotes: 0

Related Questions