PHP save array to SQL issue

Question

I used repeatedly a form and a save page to save the answers from users into my sql database. Initially I was saving the answers to an array and once the goal was reached I used a loop to pass all the values of the array to the database the code of the update page looked like this:

unset($_SESSION['myanswers']);

$_SESSION['myanswers'] = array();
        $_SESSION['myanswers']=array(
        "File"=>$_POST['file'],
        "Rating"=>$_POST['like'],
        "Repetitions"=>$_SESSION['reps'],
        "degradation"=>$_POST['degradation']
    );  


$f1=$_SESSION['myanswers']['File'];
$r1=$_SESSION['myanswers']['Rating'];
$r2=$_SESSION['myanswers']['Repetitions'];
$the_id=$_SESSION['user_id'];
$the_deg=$_SESSION['myanswers']['degradation'];


array_push($_SESSION['answers'], $_SESSION['myanswers']);
if($_SESSION['counter']==goal){

require 'connect.php';

foreach( $_SESSION['answers'] as $key => $obj)
{



    $f1=$obj['File'];
    $r1=$obj['Rating'];
    $r2=$obj['Repetitions'];
    $the_id=$_SESSION['user_id'];
    $the_deg=$obj['degradation'];

    $sql = "INSERT INTO `array`(`id`, `file`, `rating`, `repetitions`, `degredation`) VALUES ('$the_id', '$f1', '$r1', '$r2', '$the_deg')";
    mysql_query($sql);



}

header("Location: MyEndPage");
die();
}else{
    header("Location: MyFormPage");
    die();
}

This code was working fine but then I needed to save all the answers immediatelly not when the goal was reached. So I changed the code to the following:

unset($_SESSION['myanswers']);

$_SESSION['myanswers'] = array();
        $_SESSION['myanswers']=array(
        "File"=>$_POST['file'],
        "Rating"=>$_POST['like'],
        "Repetitions"=>$_SESSION['reps'],
        "degradation"=>$_POST['degradation']
    );  


$f1=$_SESSION['myanswers']['File'];
$r1=$_SESSION['myanswers']['Rating'];
$r2=$_SESSION['myanswers']['Repetitions'];
$the_id=$_SESSION['user_id'];
$the_deg=$_SESSION['myanswers']['degradation'];


require 'connect.php';
$sql = "INSERT INTO `array`(`id`, `file`, `rating`, `repetitions`, `degredation`) VALUES ('$the_id', '$f1', '$r1', '$r2', '$the_deg')";
mysql_query($sql);





if($_SESSION['counter']==goal){
    header("Location: MyEndPage");
die();
}else{
    header("Location: MyFormPage");
    die();
}

This is when the problem appeared, the new entries are saved always at the same spot, between the old and the new data. For example if before the change I had saved 5 entries their order would be like this in the sql array:

1, name1, data1
2, name2, data2
3, name3, data3
4, name4, data4
5, name5, data5

Adding repeatedly new entries with the new code was problematic if for example we add entries 6,7,8 they are added like this:

1, name1, data1 | 1, name1, data1 | 1, name1, data1
2, name2, data2 | 2, name2, data2 | 2, name2, data2
3, name3, data3 | 3, name3, data3 | 3, name3, data3
4, name4, data4 | 4, name4, data4 | 4, name4, data4
5, name5, data6 | 5, name5, data6 | 5, name5, data6
6, name6, data5 | 7, name7, data7 | 8, name8, data8
--------------------- | 6, name6, data6 | 7, name7, data5
--------------------- | --------------------- | 6, name6, data6

For some reason the insert into does not go at the end of the sql array, but is stack at the end of the data collected with the old code. I tried some tweaks on the code, but nothing worked.

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 11 '15 at 13:47
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 11 '15 at 13:47
@JayBlanchard I know that it is vulnerable to injections but the webisite is not wildly distributed, but only to trusted people so I did not care to make it strong against attacks. I will try the prepared statements and let you know if it worked, but even out of curiosity I would like to know why this happent is the current code. Thanks for your comments. — Dimitris, Nov 11 '15 at 14:13
FYI, SQL injection is not an "attack", it's a bug that causes syntax errors on runtime and makes `O'Hara` call the IT guy. — Álvaro González, Nov 11 '15 at 15:59
SQL injection can be exploited, but even if it isn't, you still risk errors that may lead to unexpected behaviour and to error messages or degraded functionality for your end users that could easily be avoided. — GolezTrol, Nov 21 '15 at 23:40

score 0 · Answer 1 · answered Nov 21 '15 at 23:41

Databases don't necessarily store new records at the end of a table. They may also reuse space that was freed by a previous delete.

Also, the order may seem random for whatever reason. Especially when you filter (where clause) or join other tables, the order may be affected because MySQL uses particular indexes to filter or join on, and will often return the actual data in that order.

Whatever the cause, if you query data you may get that data in any random order. If you need information in a specific order, enforce that order by adding an ORDER BY clause to your query.

PHP save array to SQL issue

1 Answers1