mysql UPDATE statements using associative arrays in PHP

Question

I has been read this topic Constructing UPDATE statements using associative arrays in PHP , but it was a bit different with what I need to.

I have a table t1.

+--------------+----------------+----------------+------------+
| money        | exp            | uid            | remark     |
+--------------+----------------+----------------+------------+
| 9999         | 9999           | 1              |            |
| 1234         | 567            | 2              |            |
| 8887         | 88             | 3              |            |
+--------------+----------------+----------------+------------+

I would like to create a PHP function called jnupdateuser to update this table, like:

jnupdateuser(array('money'=>10000,'exp'=>10000),1);

After that the data will be this:

+--------------+----------------+----------------+------------+
| money        | exp            | uid            | remark     |
+--------------+----------------+----------------+------------+
| 10000        | 10000          | 1              |            |
| 1234         | 567            | 2              |            |
| 8887         | 88             | 3              |            |
+--------------+----------------+----------------+------------+

If do with this:

jnupdateuser(array('remark'=>'None'),2);

than the data will be this:

+--------------+----------------+----------------+------------+
| money        | exp            | uid            | remark     |
+--------------+----------------+----------------+------------+
| 9999         | 9999           | 1              |            |
| 1234         | 567            | 2              | None       |
| 8887         | 88             | 3              |            |
+--------------+----------------+----------------+------------+

My code as below:

function jnupdateuser($jnparams = array(),$uids){

    $conditionStrings = array();
    foreach ($jnparams as $column => $value) {
        //how to create this part to update my table? or maybe I was totally wrong?
    }

    return DB::query("UPDATE ".DB::table('game_jnmx_user')." SET ??? = ??? WHERE uid = $uids "); 
}

DB::query is my template language, same as mysqli_query, DB::table also template language;

Thank you.

Maybe something like `$set = 'SET '; foreach ($jnparams as $column => $value) { $set .= $column . " = '$value', "; } $set = rtrim(', ', $set);` then use `$set` in the query. Check that `$jnparams` is set first though. This also doesn't look at multiple security issues this opens — user3783243, Jun 21 '18 at 16:09
Oh, the `rtrim` is backwards http://php.net/manual/en/function.rtrim.php. `$set` should be first. — user3783243, Jun 21 '18 at 16:12
And why can't you adapt the code in that question to your use case? Seems the only difference is the table name. There are also plenty of query builders already out there, did you try to use one or examine the source? — Devon Bessemer, Jun 21 '18 at 16:16

score 1 · Accepted Answer · answered Jun 21 '18 at 16:17

1

Try something like that:

function jnupdateuser ($jnparams = [], $uid)
{
    if (empty($jnparams)) {
        return true;
    }

    $conditions = [];
    $uid = (int) $uid;

    foreach ($jnparams as $column => $value) {
        $conditions[] = "`{$column}` = '{$value}'";
    }

    $conditions = implode(',', $conditions);

    return DB::query("UPDATE ".DB::table('game_jnmx_user')." SET {$conditions} WHERE uid = {$uid}");
}

It builds the SET from the input parameters and I added some validations. Be ware that here you should escape or sanitize in some way the data you put into the DB, cause it's vulnerable to SQL injection.

answered Jun 21 '18 at 16:17

vuryss

1,270
8
16

I'd really recommend not writing solutions that are subject to SQL injections to prevent future beginners from copying and using your code... – Devon Bessemer Jun 21 '18 at 16:22
Without knowing his DB abstraction I cannot escape the values correctly. If he's using mysqli underneath - I don't have the connection resource to use for escaping. Same with PDO. I can try to use a method which can try to escape them, but it won't be the same as using the DB for that and the answer will get way longer than it should have :D – vuryss Jun 21 '18 at 16:27
Refer to your answer, my final is like this: `function jnupdateuser($jnparams = array(),$uids){ foreach ($jnparams as $column => $value) { $a = 0; $set[$a] = addslashes($column)." = '".addslashes(($value))."' "; $a++; } $uids = intval($uids); $lset = implode(',',$set); return DB::query("UPDATE ".DB::table('game_jnmx_user')." SET $lset WHERE uid = $uids"); }` , is it any SQL injection? – Swee Hong Jun 21 '18 at 16:33
1

@SweeHong from PHP Docs for addslashes: `The addslashes() is sometimes incorrectly used to try to prevent SQL Injection. Instead, database-specific escaping functions and/or prepared statements should be used.` – vuryss Jun 21 '18 at 16:35
Actually, my template language got other statement to prevent SQL injection, `daddslashes()`, since this is not a famous template language, so I didn't use in my question to avoid confusion. – Swee Hong Jun 21 '18 at 16:39
1

@SweeHong then use that instead of the raw `addslashes` :) – vuryss Jun 21 '18 at 16:40
@vuryss Thank you. :D – Swee Hong Jun 21 '18 at 16:43

mysql UPDATE statements using associative arrays in PHP

1 Answers1