在php中看似相同的sql查询，但是一个插入额外的行

Question

I generate the below query in two ways, but use the same function to insert into the database:

INSERT INTO person VALUES('','john', 'smith','new york', 'NY', '123456');

The below method results in CORRECT inserts, with no extra blank row in the sql database

foreach($_POST as $item) 
$statement .= "'$item', ";

$size = count($statement);
$statement = substr($statement, 0, $size-3);
$statement .= ");";

The code below should be generating an identical query to the one above (they echo identically), but when I use it, an extra blank row (with an id) is inserted into the database, after the correct row with data. so two rows are inserted each time.

$mytest = "INSERT INTO person VALUES('','$_POST[name]', '$_POST[address]','$_POST[city]', '$_POST[state]', '$_POST[zip]');";

Because I need to run validations on posted items from the form, and need to do some manipulations before storing it into the database, I need to be able to use the second query method.

I can't understand how the two could be different. I'm using the exact same functions to connect and insert into the database, so the problem can't be there.

below is my insert function for reference:

function do_insertion($query) {      
    $db = get_db_connection();
        if(!($result = mysqli_query($db, $query))) {
            #die('SQL ERROR: '. mysqli_error($db));
             write_error_page(mysqli_error($db));
          } #end if     
    }

Thank you for any insite/help on this.

Answer 1

Using your $_POST directly in your query is opening you up to a lot of bad things, it's just bad practice. You should at least do something to clean your data before going to your database.

The $_POST variable often times can contain additional values depending on the browser, form submit. Have you tried doing a null/empty check in your foreach?

!~ Pseudo Code DO NOT USE IN PRODUCTION ~!

foreach($_POST as $item) 
{

    if(isset($item) && $item != "")
    {   
        $statement .= "'$item', ";

        $size = count($statement);
        $statement = substr($statement, 0, $size-3);
        $statement .= ");";
     }

}

Please read @tadman's comment about using bind_param and protecting yourself against SQL injection. For the sake of answering your question it's likely your $_POST contains empty data that is being put into your query and resulting in the added row.