MySQL Syntax with FROM with a var-CodePudding

I got some problems with my MySQL Syntax.

This is my code:

Config.SocietyMoneyTable = 'addon_account_data'

local result = MySQL.Sync.fetchAll("SELECT money FROM @account_table WHERE account_name = @society", {
            ['@account_table'] = Config.SocietyMoneyTable,
            ['@society'] = society
        })

Error:

[ERROR] [MySQL] [maze_management] An error happens on MySQL for query "SELECT money FROM
'addon_account_data' WHERE account_name = 'society_police'": ER_PARSE_ERROR: You have an
error in your SQL syntax; check the manual that corresponds to your MariaDB server version
for the right syntax to use near ''addon_account_data' WHERE account_name = 'society_police''
at line 1

The Syntax does work when I change the @account_table to the string which is in Config.SocietyMoneyTable. But I need this configed so this is no solution for me.

CodePudding user response：

A query parameter annotated with the @ sigil can only be used in place of a scalar value, not a table name or other identifier. You need to use string formatting to get your configurable table name into the query, not a query parameter.

Something like the following:

Config.SocietyMoneyTable = 'addon_account_data'

local queryString = string.format("SELECT money FROM `%s` WHERE account_name = @society",
    Config.SocietyMoneyTable)

local result = MySQL.Sync.fetchAll(queryString, {
            ['@society'] = society
        })

I have not tested this code, and I don't use Lua often, so if there are mistakes I will have to leave it to you to resolve them. But it should at least show the principle: identifiers (like table names) must be fixed in the query string, not added as query parameters.