escaped " in sql column of type json

[Ginso]

Hello,
i have a table with one column of type json. i use json_encode to create the value for the insert statement. This always worked very well, but now i have a string containg the character " like

Code: Select all

$name = 'The "Smoking Gun" tape'

and that produces the folling Error:

Unexpected error: Error while processing SQL request (leto.boardgamearena.com via TCP/IP): INSERT INTO log (`turn`, `player_id`, `card_id`, `card_deck`, `action`, `action_arg`) VALUES (1, 2331792, 0, 0, 'option', '{"type":1,"args":{"msgActive":"Ginso1 wants to play The \"Smoking Gun\" tape. ${you} can react.","msgInactive":"${actplayer} can react","targets":[],"_private":{"active":{"options":[{"id":"13","role":0,"type":2},{"id":-1,"type":5,"text":"pass"}]}}},"card":"10","secType":0}') Invalid JSON text: "Missing a comma or '}' after an object member." at position 57 in value for column 'log.action_arg'.

I already have a workaround: It works when i change the column type to VARCHAR, but i would like to know if i could get it working with the json column.

[Victoria_La]

What is the code you use to do insert?

[Ginso]

Code: Select all

$actionArgs = json_encode($args);
self::DbQuery("INSERT INTO log (`turn`, `player_id`, `card_id`, `card_deck`, `action`, `action_arg`) VALUES ($turn, $playerId, $cardId, $deckId, '$action', '$actionArgs')");

[Victoria_La]

you can try to call escape function
$actionArgs = addslashes(json_encode($args));

[mavit]

The old PHP mysql extension pushed people into using this "build your SQL by concatenating some strings" idiom by not supporting placeholders in queries, but itʼs pretty risky to expect developers to manually escape the right parts of the string every time. The new mysqli and PDO PHP extensions have better ways of doing this, but lots of people are used to doing it the old way and lots of code still follows the old pattern.

For what itʼs worth, I wrote myself a little helper function to make it easier not to forget to escape anything.

Code: Select all

function sql_placeholders (string $sql, string ...$args) {
    if ( substr_count($sql, '?') != count($args) ) {
        throw new BgaSystemVisibleException(
            'Insufficient arguments for pattern'
        );
    }

    foreach ( $args as $arg ) {
        $sql = preg_replace(
            '/\?/',
            "'". self::escapeStringForDB($arg). "'",
            $sql,
            1
        );
    }

    return $sql;
}

…

self::DbQuery(
     self::sql_placeholders(
         'insert into `player`
          set `player_id` = ?,
              `player_color` = ?,
              `player_canal` = ?,
              `player_name` = ?,
              `player_avatar` = ?',
         $player_id,
         $color,
         $player['player_canal'],
         $player['player_name'],
         $player['player_avatar']
     )
);

For background, it's worth reading up on SQL injection at, e.g., https://owasp.org/www-community/attacks/SQL_Injection.

[mavit]

Also, feel free to give suggestion https://boardgamearena.com/bug?id=30031 a thumbs up if you'd like the BGA database API to help you out with this.