Insert query missing columns

Question

I have a form that is using $_post to get to an insert.php page. The $_post info looks like this when I do a print_r

Array ( 
    [extension] => Array ( 
        [0] => 100 
        [1] => 101 
        [2] => 102 
        ) 
    [secret] => Array ( 
        [0] => a467ca4044f298eff15a26e59f39fe21 
        [1] => 0c4275de171ef363b77aa6aae27afff1 
        [2] => c1951bfb07ed6a833d6d785ff4e19123 
        ) 
    [phone] => Array ( 
        [0] => 80828703658A 
        [1] => 80828703D858 
        [2] => 80828703F866 
        ) 
    [template] => Array ( 
        [0] => Another 600 Template 
        [1] => Another 600 Template 
        [2] => Another 600 Template 
        ) 
)

The insert.php page only inserts the data from extension and secret. Not the phone or template data. The phone and template data gets into the array by dropdown boxes in the original form. here's the code I am using

// Escape user inputs for security
$ext = mysqli_real_escape_string($link, $_POST['extension']);
$secret = mysqli_real_escape_string($link, $_POST['secret']);
$macaddress = mysqli_real_escape_string($link, $_POST['phone']);
$templatename = mysqli_real_escape_string($link, $_POST['template']);

// attempt insert query execution
$sql = "INSERT INTO assignments 
                (id, extension, secret, macaddress, template) 
        VALUES  (null,'$ext', '$secret', '$macaddress', '$templatename')";

if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

// close connection
mysqli_close($link);
?>

Where am I going wrong? Thanks

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 25 '17 at 21:19

score 0 · Accepted Answer · edited May 23 '17 at 12:24

0

1) they are arrays so you need to process them in a loop

2) Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

3) You dont need to pass NULL to the id column if its AutoIncrement, mysql will look after it automatically

// attempt insert query execution
$sql = "INSERT INTO assignments 
                (extension, secret, macaddress, template) 
        VALUES  (?,?,?,?)";

$result = $link->prepare($sql);

foreach ($_POST['extension'] as $idx => $extention) {
    $result->bind_param('ssss',
                        $extension,
                        $_POST['secret'][$idx],
                        $_POST['phone'][$idx],
                        $_POST['template'][$idx]
                        );

    if( $result->execute() ) {
        echo "Records $idx added successfully.";
    } else{
        echo "ERROR: Could not execute $sql. " . $result->error;
        exit;
    }
}

// close connection
mysqli_close($link);
?>

edited May 23 '17 at 12:24

Community

1
1

answered Jan 25 '17 at 21:26

RiggsFolly

93,638
21
103
149

Thanks RiggsFolly Using your suggestion I get a "Page not working" error – Ed Lentz Jan 25 '17 at 22:02
I turned on error reporting and I am getting this Fatal error: Call to undefined method mysqli_stmt::bind_parm() in /var/www/html/cqadmin/Assign/temp_update.php on line 15 Line 15 is the result->bind_parm I tried to change the variable type from ssss to ssii with no luck, it still inserts the extension and the secret into the db. RiggsFolly, did you post a comment about sanitizing?? If you did I briefly read it and now it appears to be gone. – Ed Lentz Jan 26 '17 at 18:24
Sorry TYPO, fixed it – RiggsFolly Jan 26 '17 at 19:01
No worries, where was the typo? I've been reading up on the bind_param and the methods you are employing. Don't understand it yet, but ... – Ed Lentz Jan 26 '17 at 21:25
Found a spelling error extention changing to extension and voila I get all 4 data sets inserted! – Ed Lentz Jan 26 '17 at 21:39

Insert query missing columns

1 Answers1