PHP to using implode on an array to format for sql insert

Question

I am trying to implode an array into a sql statement. My question is, how is the best way for the values to have quotations around strings, and dates, etc, but not around int values?

-edit- New code below, The issue i was hainv was using backticks instead of quotes on values, thanks Mark Baker, As far as prepared statements to stop sql injection, will the below code suffice? the column names are set by the code, and all vaules are myslqi_real_escape'ed.

    $HardwareDistribution["HardwareID"]=mysqli_real_escape_string($con,$_POST["HardwareID"]);
    $HardwareDistribution["UserID"]=mysqli_real_escape_string($con,$_POST["UserID"]);
    $HardwareDistribution["DateGiven"]=mysqli_real_escape_string($con,$_POST["DateGiven"]);
    $HardwareDistribution["ConditionGiven"]=mysqli_real_escape_string($con,$_POST["ConditionGiven"]);
    $SQL_AssignHardware="INSERT INTO HardwareDistribution (`".implode("`,`",array_keys($HardwareDistribution))."`) VALUES ('".implode("','",array_values($HardwareDistribution))."')";
    mysqli_query($con,$SQL_AssignHardware);

String values are quoted with `'`, not with backticks.... the latter are for table and/or column names — Mark Baker, Aug 14 '13 at 15:07
SQL Injection just waiting to happen - if you're using MySQLI, then learn to use prepared statements — Mark Baker, Aug 14 '13 at 15:07
If you are using the insert one time prepared statements are overhead, because more round trips are needed. One to prepare the statement, more to copy the data and one to destory the prepared statement to clear memory on the SQL server — Raymond Nijland, Aug 14 '13 at 15:32

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

0

Use is_int():

foreach($HardwareDistribution as $value) {
    if(is_int($value)) {
        //execute your query here
    }
    else {
        //execute your query with quotations here
    }
}

Also, as others have suggested, you might want to take a look at prepared statements to prevent SQL injection.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 14 '13 at 15:08

Amal Murali

75,622
18
128
150

score 0 · Answer 2 · answered Aug 14 '13 at 15:09

0

You could add the quotes around the values that need quotes when you load them into the array. As mentioned, you probably also want to take a look into prepared statements

answered Aug 14 '13 at 15:09

StephenTG

2,579
6
26
36

score 0 · Answer 3 · answered Aug 14 '13 at 15:12

The best way would be to craft your MySQL statement with defined column names and place holders for values. If the query is to be dynamic, then you should have—at the very lease—a “whitelist” of allowed column names so your database doesn’t become vulnerable to mass assignment attacks.

PHP to using implode on an array to format for sql insert

3 Answers3