PHP: preparing data array for secure insertion into MySQL

Question

I'm working on a project that requires (secure) insertion of data arrays into a MySQL database. My code currently iterates through an XML document using the foreach construct, parsing all the data I've chosen to extract from it.

Here's the code...

foreach ($xml->town as $towns) {

        # -> player information
        $player_id = mysqli_real_escape_string($dbconnect,$towns->player->playername['id']);
        $player_name = mysqli_real_escape_string($dbconnect,$towns->player->playername);

        # -> town data information
        $town_name = mysqli_real_escape_string($dbconnect,$towns->towndata->townname);
        $town_id = mysqli_real_escape_string($dbconnect,$towns->towndata->townname['id']);
        $founded_on = mysqli_real_escape_string($dbconnect,$towns->towndata->foundeddatetime);
        $population = mysqli_real_escape_string($dbconnect,$towns->towndata->population);
        $capital = mysqli_real_escape_string($dbconnect,$towns->towndata->iscapitalcity);

        # -> alliance info
        $alliance_cap = mysqli_real_escape_string($dbconnect,$towns->towndata->isalliancecapitalcity);

        # -> location information
        $map_x = mysqli_real_escape_string($dbconnect,$towns->location->mapx);
        $map_y = mysqli_real_escape_string($dbconnect,$towns->location->mapy);

        # -> terrain information
        $terrain_type_id = mysqli_real_escape_string($dbconnect,$towns->location->terraintype['id']);
        $terrain_type = mysqli_real_escape_string($dbconnect,$towns->location->terraintype);
        $terrain_overall_id = mysqli_real_escape_string($dbconnect,$towns->location->terrainoveralltype['id']);
        $terrain_overall_type = mysqli_real_escape_string($dbconnect,$towns->location->terrainoveralltype);

In a question I recently asked about XML parsing, someone commented that I'm not using mysqli_real_escape_string correctly. He mentioned that I should first extract the data, then later call mysqli_real_escape_string in preparation of the database insert operation.

MySQL query

I've written a straightforward MySQL query which works; though for some reason its only inserting the first row of the array, rather than the entire array of data.

mysqli_query($dbconnect, 
        
            "INSERT INTO towndata (player_id, playername, town_name, town_id, founded_on, population, capital, alliance_cap, map_x, map_y, terrain_type_id, terrain_type, terrain_overall_id, terrain_overall_type) 
            VALUES ('$player_id', '$player_name', '$town_name', '$town_id', '$founded_on', '$population', '$capital', '$alliance_cap', '$map_x', '$map_y', '$terrain_type_id', '$terrain_type', '$terrain_overall_id', '$terrain_overall_type')"
            
        ); # end mysql statement
} # end of _foreach_ loop

Now for the question/problem portion of things...

Why is only the first row being inserted instead of the entire data array?

As mentioned above, the INSERT INTO statement works. However I can't seem to figure out why my code is only inserting the first row. Other portions of the application use the same code, but are not exhibiting this behavior, which leaves me entirely clueless as to the cause.
What is the correct/effective way to use mysqli_real_escape_string to prepare data arrays (and strings) for secure database insertion?

I realize this may be a question of preference; but I can't help but wonder if there's a pattern or paradigm that I'm not familiar with.

Thank you in advance for your time, help and advice.

first way to debug is to echo the sql statement – Kevin Jan 02 '15 at 09:27 — Kevin, Jan 02 '15 at 09:27

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

Your INSERT seems to work only for the first cycle of the loop. I suggest you to print out the insert string for every cycle and without executing it, and then test your insert statements from your MySQL client or phpMyAdmin. Probably you will have a primary key violation (you can check it this way) or another error depending on the data after first row.

Instead of using mysqli_real_escape_string, I suggest you to take a look at MySQLI prepared statements, as they will provide you the correct way to write an INSERT statement avoiding SQL injection issues.

Prepared statements

edited May 23 '17 at 10:26

Community

1
1

answered Jan 02 '15 at 09:28

kiks73

3,718
3
25
52

there was indeed a primary key violation or something of the sorts. I logged into phpmyadmin and changed the primary key to a different column, and it instantly solved the first problem. If I may, I'd like to ask a quick follow question... is there any way to get the PHP interpreter to print mysql errors like primary key violations? – Perp1exed Jan 02 '15 at 10:48

score 1 · Accepted Answer · answered Jan 02 '15 at 09:46

$mysqli = new mysqli("localhost", "my_user", "my_password", "xxxx");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

foreach ($xml->town as $towns) {

        # -> player information
        $player_id = $towns->player->playername['id'];
        $player_name = $towns->player->playername;

        # -> town data information
        $town_name = $towns->towndata->townname;
        $town_id = $towns->towndata->townname['id'];
        $founded_on = $towns->towndata->foundeddatetime;
        $population = $towns->towndata->population;
        $capital = $towns->towndata->iscapitalcity;

        # -> alliance info
        $alliance_cap = $towns->towndata->isalliancecapitalcity;

        # -> location information
        $map_x = $towns->location->mapx;
        $map_y = $towns->location->mapy;

        # -> terrain information
        $terrain_type_id = $towns->location->terraintype['id'];
        $terrain_type = $towns->location->terraintype;
        $terrain_overall_id = $towns->location->terrainoveralltype['id'];
        $terrain_overall_type = $towns->location->terrainoveralltype;

/* create a prepared statement */
if ($stmt = $mysqli->prepare("INSERT INTO `towndata` (`player_id`, `playername`, `town_name`, `town_id`, `founded_on`, `population`, `capital`, `alliance_cap`, `map_x`, `map_y`, `terrain_type_id`, `terrain_type`, `terrain_overall_id`, `terrain_overall_type`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")) {

/* bind parameters for markers */
$stmt->bind_param('ssssssssssssss', $player_id, $playername, $town_name, $town_id,$founded_on, $population, $capital, $alliance_cap,$map_x, $map_y, $terrain_type_id, $terrain_type,$terrain_overall_id, $terrain_overall_type);

/* execute query */
$stmt->execute();
}
}

where

i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets

so in the above code you can alter the 'sssssssssssssss' part in parameter binding if not all variables are strings.

PHP: preparing data array for secure insertion into MySQL

2 Answers2