Dynamic MySQL Query with PHP

Question

I am looking for a way to make dynamic queries to my MySQL server. At the moment this is the code I use to update data on the server:

$deskAttr = json_decode($_POST["desk_attributes"]);

foreach($deskAttr as $key => $value) {
    $sql = "UPDATE desk_attributes SET iw_standard=".$value->iw_standard.", avaya_standard=".$value->avaya_standard.", avaya_withcallid=".$value->avaya_withcallid.", avaya_withtransfer=".$value->avaya_withtransfer.", dual_screen=".$value->dual_screen.", air_conditioning=".$value->air_conditioning.", iw_obdialler=".$value->iw_obdialler." WHERE id=".$value->id;
    $conn->query($sql);
}

As you can see, the SQL column names are the same as thedeskAttrkeys. I'm looking for a way to make this line a loop so, that I don't need to change this line if I were to add more columns to the MySQL table.

It would look something like this:

$deskAttr = json_decode($_POST["desk_attributes"]);

foreach($deskAttr as $key => $value) {  
    $sql = "UPDATE desk_attributes SET";
    foreach($value as $k => $v) {
        $sql .= " $k = $value->$k ,";
    }
    $sql .= "WHERE id=".$value->id";
}

How would I write the code above so it will actually work?

**EDIT**

Maybe it will be helpful to know that$deskAttr is an array of objects, and the name of the columns are the same as the name of the objects keys.

Here is what I mean in pseudo code:

foreach($object in $deskAttr) {
    $sql = "UPDATE table SET ";
    foreach($key in $object) {
        if($key != "id")
            $sql .= "$key = $object->$key, ";
    }
    $sql .= "WHERE id = $object->id;
    $conn->query($sql);
}

Obviously this would add an extra comma at the end of the query before the WHERE part, but hopefully you get what I'm trying to achieve.

You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of concatenating your queries. Specially since you're not escaping the user inputs at all! — M. Eriksson, Jul 18 '17 at 09:41
You should really switch to prepared statements for the values and a white-list for the column names to avoid sql injection. — jeroen, Jul 18 '17 at 09:41
You can't bind column names via prepared statement. So if you're gonna put a variable string in your SQL query string, juste escape them correctly and you're fine. — Mouradif, Jul 18 '17 at 11:01

Pupil · Accepted Answer · 2017-07-18T11:24:07.593

1

You can do it with slight change in your code by using PHP's implode() function.

Take a blank array, concatenate the update parameters to it.

And then if is not empty(), implode() to get string.

Updated Code:

$sql = "UPDATE desk_attributes SET ";
foreach ($deskAttr as $key => $value) {
 $value = mysqli_real_escape_string($link, $value); // $links is database connection string.
 $key = mysqli_real_escape_string($link, $key); // $links is database connection string.
 $updtAttrs[] = $key ." = '" . $value . "'";
}
$sql .= ! empty($updtAttrs) ? implode(', ', $updtAttrs) : '';
$sql .= " WHERE id=" . $value->id;

edited Jul 18 '17 at 11:24

answered Jul 18 '17 at 09:50

Pupil

23,834
6
44
66

2

This code is **_very_** insecure. It's wide open to SQl Injections. You should use Prepared Statements instead of concatenating user inputs directly like this. You should also have a list of allowed column names. – M. Eriksson Jul 18 '17 at 10:08
No it's not. Stop being a prepared-statement-nazi and go read `mysqli_real_escape_string` documentation – Mouradif Jul 18 '17 at 11:00
@KiJéy - Maybe you should check the time line of the comment and the edits of the post. **1.** When I wrote the comment, `mysqli_real_escape_string()` weren't included in the answer _at all_. **2.** The `$key` is _still_ totally unescaped, which _still_ makes it 100% open for SQL Injections. Btw, there actually are situations where `mysqli_real_escape_string()` isn't enough: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/12118602#12118602 – M. Eriksson Jul 18 '17 at 11:19
@MagnusEriksson I have updated the answer after getting comment from you. – Pupil Jul 18 '17 at 11:22
The code is still open for injections, as you can see in my last comment. – M. Eriksson Jul 18 '17 at 11:23
1

If I post some data that looks like this: `$_POST['something hello'] = 'world'`, that query would break. As mentioned earlier, you should have a white list of allowed column names. It a column name isn't white listed, don't add it to the query. Never trust user input. – M. Eriksson Jul 18 '17 at 11:29

Dynamic MySQL Query with PHP

1 Answers1