[C++/Lua] Native db.query function

[z3r002557 4]

Hey everyone,

I decided to share a simple, native C++ implementation for database queries. I've noticed that some bases out there still rely on the old mysql_query Lua function (the one that uses os.execute to open the terminal, run the query, and generate a temporary text file in /tmp).

The main issue with that legacy method is that it halts the main game thread. During peak moments, such as guild wars, this causes the notorious "step lag" / "core lag". The code below creates a native db.query function utilizing the direct connection from the DBManager, making the execution instantaneous and completely lag-free.

 

##  Source Implementation (Game)

 

 

Step 1: questlua.cpp

Open the file game/src/questlua.cpp and search for the ScriptToString function. Right after its closing bracket (the last }), skip a line and paste the new code.

Example of how it should look:
 

lua_settop(L,x);
        return retstr;
    } // <-- End of ScriptToString

    // --- START NATIVE DB.QUERY ---
    int db_query(lua_State* L)
    {
        if (!lua_isstring(L, 1))
            return 0;

        const char* szQuery = lua_tostring(L, 1);
        if (!szQuery || strlen(szQuery) == 0) 
            return 0;

        SQLMsg* pMsg = DBManager::instance().DirectQuery(szQuery);
        if (pMsg) 
        {
            delete pMsg;
        }
        return 0;
    }

    void RegisterDBFunctionTable()
    {
        luaL_reg db_functions[] = 
        {
            { "query",      db_query        },
            { NULL,         NULL            }
        };
        CQuestManager::instance().AddLuaFunctionTable("db", db_functions);
    }
    // --- END NATIVE DB.QUERY ---

 

 

Step 2: Still in questlua.cpp

In the exact same file, search for RegisterHorseFunctionTable();. Skip a line below it and register our new function.

Example of how it should look:
 

RegisterHorseFunctionTable();
        
        RegisterDBFunctionTable();

 

 

Step 3: questlua.h

Open game/src/questlua.h, look for the registry list (where the extern void Register... declarations are located), and add the following line:

extern void RegisterDBFunctionTable();

 

 

##  Final Steps

Now simply compile your source and replace the game executable in your server.

Go to your quests folder (e.g., share/locale/english/quest), open the quest_functions file, and add the following line: db.query

 

 

##  Quest Usage Example

You can use string.format to build your queries in a clean and safe way. Here is an example of a kill counter updating the kill column inside the player table:

quest ranking_kills begin
    state start begin
        when kill with npc.is_pc() begin
            local pid = pc.get_player_id()
            
            -- Direct, fast query with no core lag
            local query = string.format("UPDATE player.player SET kill = kill + 1 WHERE id = %d", pid)
            db.query(query) 
        end
    end
end

 

 

## Caveats and Limitations

Before applying this to all your systems, please keep two things in mind:

Execution Only (Does not read SELECTs): This function was built specifically for write performance (UPDATE, INSERT, DELETE, REPLACE). Due to the delete pMsg; command in the C++ code, it does not return SELECT rows into Lua tables. If you try to fetch data, it will return 0 or nil.

Beware of SQL Injection: Never place an input() command directly inside db.query without strictly sanitizing it first. If you ask a player to type a string and feed it directly into the query, a malicious user could run commands to drop your tables. Always use safe, native source functions like pc.get_player_id(), pc.get_name(), etc., or heavily restrict the allowed input characters.

[Gurgarath 3694]

Hello,

Thank you for your release. Please note that you can directly sanitize inputs inside your C++ function by using the EscapeString function, so that all queries are sanitized as best as it can regardless.

[Syreldar 2737]

We already have mysql_direct_query.

every modern server pretty much uses this btw.

[z3r002557 4]

15 hours ago, Syreldar said:

We already have mysql_direct_query.

every modern server pretty much uses this btw.

 

16 hours ago, Gurgarath said:

Hello,

Thank you for your release. Please note that you can directly sanitize inputs inside your C++ function by using the EscapeString function, so that all queries are sanitized as best as it can regardless.

 

 

#include <memory> // Para std::unique_ptr

int db_query(lua_State* L)
{
    if (!lua_isstring(L, 1))
    {
        lua_pushnumber(L, 0);
        lua_newtable(L);
        return 2;
    }

    const char* szQuery = lua_tostring(L, 1);
    
    // Executa a query usando std::unique_ptr para evitar Memory Leak (substitui o auto_ptr antigo)
    std::unique_ptr<SQLMsg> pMsg(DBManager::instance().DirectQuery(szQuery));
    
    if (!pMsg || !pMsg->Get())
    {
        lua_pushnumber(L, 0);
        lua_newtable(L);
        return 2;
    }

    // Retorno 1: Número de linhas afetadas ou encontradas
    lua_pushnumber(L, (double)pMsg->Get()->uiAffectedRows);

    // Retorno 2: Tabela de resultados
    lua_newtable(L);

    MYSQL_RES* result = pMsg->Get()->pSQLResult;
    if (result && pMsg->Get()->uiAffectedRows > 0)
    {
        MYSQL_ROW row;
        MYSQL_FIELD* fields = mysql_fetch_fields(result);
        int num_fields = mysql_num_fields(result);
        int m = 1;

        while ((row = mysql_fetch_row(result)))
        {
            lua_pushnumber(L, m);
            lua_newtable(L); // Tabela da linha

            for (int i = 0; i < num_fields; ++i)
            {
                // Usa o nome da coluna como CHAVE da tabela (Ex: row.name)
                lua_pushstring(L, fields[i].name);

                if (row[i] == nullptr)
                {
                    lua_pushnil(L);
                }
                else if (IS_NUM(fields[i].type))
                {
                    // Auto-detecta número e envia como número para o Lua
                    lua_pushnumber(L, atof(row[i]));
                }
                else
                {
                    // Envia como string
                    lua_pushstring(L, row[i]);
                }
                lua_rawset(L, -3);
            }
            lua_rawset(L, -3);
            m++;
        }
    }

    return 2; // Retorna a contagem e a tabela
}

int db_escape(lua_State* L)
{
    if (!lua_isstring(L, 1))
    {
        lua_pushstring(L, "");
        return 1;
    }

    const char* szSource = lua_tostring(L, 1);
    // Buffer dinâmico baseado no tamanho da string de entrada (mais seguro)
    std::vector<char> szDest(strlen(szSource) * 2 + 1);
    
    DBManager::instance().EscapeString(szDest.data(), szDest.size(), szSource, strlen(szSource));
    
    lua_pushstring(L, szDest.data());
    return 1;
}

void RegisterDBFunctionTable()
{
    luaL_reg db_functions[] = 
    {
        { "query",      db_query  },
        { "escape",     db_escape },
        { NULL,         NULL      }
    };
    CQuestManager::instance().AddLuaFunctionTable("db", db_functions);
}

Fix Version

Go to your quests folder (e.g., share/locale/english/quest), open the quest_functions file, and add the following line: db.query and db.escape

 

Edited May 15 by z3r002557