Safer Database Code in FiveM Scripts with oxmysql: Placeholders, Atomic Updates and Transactions

FiveM server administrators often face database-related bugs that can harm their economy. These issues generally stem from poor database programming practices. This article outlines four habits that can help prevent most of these problems when using the oxmysql MySQL resource in FiveM server-side Lua scripts.

1. Always use placeholders for parameter values in queries. This prevents malicious input like "OR 1=1" from altering the query's functionality. Oxmysql provides placeholder helpers such as MySQL.single, MySQL.scalar, MySQL.insert, and MySQL.update, each tailored for different return types. Properly using these helpers can make code more readable and intentions clearer.

2. Perform checks and updates in a single statement. For example, updating an account's balance after verifying a sufficient balance avoids race conditions where multiple users might try to spend the same balance simultaneously. By incorporating the condition directly into the UPDATE query and checking the number of affected rows, you can ensure atomicity and prevent unintended side effects.

3. Use transactions for any multi-step database operations. Complex actions like selling a vehicle involve updating multiple tables. Encapsulating these steps in a transaction ensures that either all changes succeed, or none do, maintaining data integrity. If a part of the transaction fails, the entire operation is rolled back, preventing partial updates that could corrupt the database.

4. Batch queries when possible, especially in loops. Fetching large amounts of data step-by-step from a server restart can generate numerous unnecessary database calls, slowing down the server. Instead, retrieve all required data in a single query and process it in Lua. This approach significantly reduces the number of round trips to the database, improving performance and stability.