How to insert data from array

Question

I want to send data in the database through an array but it is not inserting.

<?php
if(isset($_POST['submit'])){
    $name=$_POST['name'];
    $last=$_POST['last'];
    $des=$_POST['des'];
    $insData =  array( $name,$last,$des);
    foreach ($insData as $key => $val){
        echo $val."<br>";
    }
    $v  = implode(", ", $val);
    $query=mysqli_query($connect,"insert into name(name,last,des) VALUES ('$v')");
    if($query){
      echo "ok data";
    }
    else {
    echo mysqli_error($connect);
    }
}   
?>

Your code is vulnerable to SQL injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com/ gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. — ADyson, Aug 31 '17 at 11:28
Anyway, check for errors and also your variable's values. Most likely $val has no value during the implode call, since you try to use it outside the scope of the foreach loop. Did you mean to implode $insData instead? But like I said above, don't do it like this, use proper parameterised queries unless you want to leave yourself vulnerable to hacking. Any tutorial on mysqli which is worth reading should show how to do this already. — ADyson, Aug 31 '17 at 11:29
Your code has logical errors, and as others said, its really ulnerable to injections too. You should go a few steps back and look at some tutorials on how to insert data into a database. We could actually correct your code, but you won't learn anything from it. As I see, you lack in the basics of programming, also including variable scope and what happens to variables inside a loop. — Twinfriends, Aug 31 '17 at 11:38

score 1 · Answer 1 · answered Aug 31 '17 at 11:39

Take a look at this line:

$query=mysqli_query($connect,"insert into name(name,last,des) VALUES ('$v')");

if $v were equal to say delboy1978uk,something,somethingelse, then your SQL would look like:

$query=mysqli_query($connect,"insert into name(name,last,des) VALUES ('delboy1978uk,something,somethingelse')");

As you can see, that is only one string. So you need to implode with some single quotes too.

$array = ['delboy1978uk', 'something', 'somethingelse'];
$v  = implode("','", $array);
echo $v;

which would output :

delboy1978uk','something','somethingelse

Meaning your SQL string should now be valid.

Bear in mind though that you are better using bound parameters to secure against SQL injection! See here http://php.net/manual/en/mysqli-stmt.bind-param.php

score 0 · Answer 2 · answered Aug 31 '17 at 11:43

You can try this

Simple but not secure :

<?php
    if(isset($_POST['submit'])){
        $name=$_POST['name'];
        $last=$_POST['last'];
        $des=$_POST['des'];
        $insData =  array( $name,$last,$des);
     foreach ($insData as $key => $val) {
        echo $val."<br>";
     }
     $v  = implode("', '", $insData);
        $query=mysqli_query($connect,"insert into name(name,last,des) VALUES ('$v')");
        if($query){
            echo "ok data";
        }
        else
        {
            echo mysqli_error($connect);
        }
    }   
?>

score 0 · Answer 3 · answered Aug 31 '17 at 12:00

0

Your code is poorly formatted (see PSR-1 and PSR-2). While you have added some error handling, you neglected to tell us what the code you have written is telling you.

There are at least 2 reasons the code you've shown us won't insert data:

$connection is never initialized
as your output is already showing you, the SQL string is nonsense. If you send the string to the screen instead of (or as well as) the database, you will see something like this:

insert into name(name,last,des) VALUES ('Zeenat, Nazir, des')

but your query should be of form:

insert into name(name,last,des) VALUES ('Zeenat', 'Nazir', 'des')

Your code is vulnerable to SQL injection.

Leaving aside the issue about the connection, a better way to build your SQL would be:

 $sql="insert into name(name,last,des) VALUES ('"
     . mysqli_real_escape_string($connection, $_POST['name']) . "','"
     . mysqli_real_escape_string($connection, $_POST['last']) . "','"
     . mysqli_real_escape_string($connection, $_POST['des']) . "')";

answered Aug 31 '17 at 12:00

symcbean

45,607
5
49
83

parameterised queries would be even better. Contrary to a seemingly popular understanding, mysqli_real_escape_string doesn't provide full protection against all forms of injection attack, and does not guarantee to do so, since, apart from anything else, that isn't the intended purpose of the function. https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string makes for a good read. The same problem applies to the mysqli version. – ADyson Aug 31 '17 at 13:47
@ADyson: I got really excited here reading your comment - a concrete example of mysql[i]_real_escape_string() not working - but this is not an example of that at all. Its just an example of a (common?) assumption about preventing SQL injection. It is far from proof that parameter binding is superior, and mysql[i]_real_escape_string() remains the only way to deal with variable argument lists. – symcbean Aug 31 '17 at 14:05
I never said it didn't work as intended, I said it didn't prevent SQL injection. Your answer implies that it does - you say "Your code is vulnerable to SQL injection", followed by "...a better way to build your SQL would be..." and then an example which is still vulnerable to SQL injection. So I thought it might be sensible to clarify that. – ADyson Aug 31 '17 at 14:20
And you can have variable argument lists using parameter binding, if you write some wrapper code to dynamically create the parameters based on the number of variables supplied to the function. You have to do some work to verify the correct data type to use for each parameter (unless you require the caller to tell you that as well), but it's not too difficult since with mysqli you can only specify one of 4 possible types. I wrote a library for some PHP projects which does exactly this, very reliably. – ADyson Aug 31 '17 at 14:21
"an example which is still vulnerable to SQL injection" - the link you provided explains that if you don't quote the output of mysql[i]_real_escape_string() then you are vulnerable to SQL injection. The example I gave quotes the output of the function. Can you provide an example of how my code is vulnerable to SQL injection? – symcbean Aug 31 '17 at 14:36
ok good point, I hadn't spotted that. Since all the variables are all strings, this will be safe. But relying on it for integers is still potentially a problem, so overall I'd still maintain that the parameterised query route is a safer one. – ADyson Aug 31 '17 at 15:24

How to insert data from array

3 Answers3