2

SOLVED

Rows 1, 4, and 10 have a field with a single quote character in them, so I was SQL injecting myself. More reason to switch to prepared statements!

I have a very simple bulk UPDATE query sent from my PHP form which, oddly enough, only affects certain rows. Rows 1, 4, and 10 doesn't get updated; but all other rows get updated.

Here is the query:

$query = "SELECT * FROM SkillDescriptions";
$result = mysql_query($query);

for ($i=1; $i<=mysql_num_rows($result); $i++){

    $skillKey = 'Skill' . (string)$i;
    $categoryKey = 'Category' . (string)$i;
    $descriptionKey = 'Description' . (string)$i; 

    $sql="UPDATE SkillDescriptions SET 
         Skill='$_POST[$skillKey]', 
         Category='$_POST[$categoryKey]', 
         Description='$_POST[$descriptionKey]'
         WHERE id='$i'
         ";
    $result2=mysql_query($sql);    

}

I've got a parallel form that does the exact same PHP form processing as this one but has a different database table, which works properly: so the problem most likely lies in the database table (configuration?) and not the code.

Why do only certain rows get updated, in a seemingly random pattern?

UPDATE

Before:

https://i.stack.imgur.com/2hk2l.png

After: (I appended test to the columns)

https://i.stack.imgur.com/ObMn5.png

chakeda
  • 1,551
  • 1
  • 18
  • 40
  • The rows where `id='$i'` will be updated. Beyond that, it is rather hard to say what does and doesn't get updated without specific data and values for the variables. – Gordon Linoff Jan 05 '15 at 01:57
  • Are you absolutely sure your database ID's are sequential? Perhaps there are missing numbers. And you could be sql-injecting yourself, causing specific queries to fail... – jeroen Jan 05 '15 at 02:09
  • @GordonLinoff What data and values do you need for clarification? – chakeda Jan 05 '15 at 02:17
  • What do rows 1-10 look like before the update? After the update? What is the value of `$i`? As far as I can tell, your code should only update one row, so I have no idea where the question is coming from. – Gordon Linoff Jan 05 '15 at 02:18
  • @jeroen I added the ID column to the table because it previously did not have one. Could that be a problem? Should I remake the table? – chakeda Jan 05 '15 at 02:19
  • 1. You should not rely on sequential ID's, if you have for example an auto-incrementing ID, you should add that to the form as a hidden field. 2. A simple `'` character in your input will break your query. This is the perfect case for a prepared statement (in PDO or mysqli...) that you execute multiple times without any risk of sql injection. – jeroen Jan 05 '15 at 02:28
  • @GordonLinoff I have added the information. I appended "test" to the values of the first column through the form. As you can see, rows 1 and 4 are unchanged. Are those values locked? – chakeda Jan 05 '15 at 02:28
  • You have a security hole in your code, please update your code to use the accepted answer here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Abhi Beckert Jan 05 '15 at 02:29
  • 1
    @jeroen I think you have answered my question: rows 1 and 4 have a ' (single quote) character in the fields. Looks like I will have to switch to PDO prepared statements. Thank you!!! – chakeda Jan 05 '15 at 02:32
  • Using MySQLi will probably be easier to learn than PDO, it's much closer to what you've already learned. But personally I prefer PDO. – Abhi Beckert Jan 05 '15 at 02:38

0 Answers0