Board Game Arena

Posted: **11 December 2020, 11:34**

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.

Posted: **11 December 2020, 14:45**

What is the code you use to do insert?

Posted: **12 December 2020, 00:04**

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')");

Posted: **13 December 2020, 05:17**

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

Posted: **14 December 2020, 12:52**

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.

Posted: **14 December 2020, 13:13**

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.

Board Game Arena

escaped " in sql column of type json

escaped " in sql column of type json

Re: escaped " in sql column of type json

Re: escaped " in sql column of type json

Re: escaped " in sql column of type json

Re: escaped " in sql column of type json

Re: escaped " in sql column of type json