MySQL

Our codebase makes a great use of BYOND's MySQL API for data storage. SQL queries have proven to be relatively fast for their current purpose, and can easily transfer a lot of data. All database work within our codebase is done with the global connection variable called dbcon.

Basic Usage

The process of using a MySQL query in DM has roughly four steps:

• Establish/check connection.
• Create query.
• Execute query.
• Verify and handle return.

Connection Checking

Connection is checked for simply by utilizing the proc/establish_db_connection(DBConnection/con) proc. It is possible to also call DBConnection/proc/IsConnected() on a database connection object itself, but the benefit of establish_db_connection() is that it will re-establish a connection if it's missing. Or try to, anyways. Both return boolean indicating whether or not a connection is valid, this should definitely be handled gracefully.

Query Creation & Execution

Once connection is up, you will have to create a new local DBQuery object. Easiest helper for this is DBConnection/proc/NewQuery(sql_query, cursor_handler = con_cursor). Pass your query text as the first argument and the function will return a new DBQuery object. Then simply call DBQuery/proc/Execute() on the object and your query is executed! Be wary: database queries are blocking. So overly large datasets without indexes to improve efficiency will bog down the game.

Data Handling

You can now verify the return data and query itself. The helpers for this are:

• DBQuery/proc/Error() - Returns TRUE if the query encountered an error.
• DBQuery/proc/ErrorMsg() - Returns a string error message if the query encountered an error. null if no error was encountered.
• DBQuery/proc/RowsAffected() - Returns a num indicating how many rows were affected by a given statement. Refer to MySQL documentation with regards to its return value nuances.
• DBQuery/proc/RowCount() - Returns a num indicating how many rows were returned from a SELECT statement.

The final step is to handle the return data. First, note that all data, regardless of MySQL type, is returned as a string in DM. So you will have to convert it to your required datatype manually.

The data of a DBQuery object is accessible in its item array. This contains all the columns of the currently selected row, all numerically indexed. Before accessing it, you must call DBQuery/proc/NextRow() at least once to populate it. Keep calling it to loop through all returned rows. Note that said method will return boolean if there is a next row, so it can easily be used in a while loop.

Complete Example

if (!establish_db_connection(dbcon))
	// No connection.
	return

var/DBQuery/query = dbcon.NewQuery("SELECT a_number, a_string, json_string FROM ss13_a_table LIMIT 10")
query.Execute()

if (query.ErrorMsg())
	// Handle error.
	return

while (query.NextRow())
	var/number = text2num(query.item[1])
	var/text = query.item[2]
	var/list/things = json_decode(query.item[3])

Data Safety

Be very mindful of embedding variables into your SQL queries!

SQL injection attacks can happen, and that should be taken into account whenever developing with SQL. The codebase has two primary means of preventing them: manual data sanitization and parameterized queries. The former involves the coder manually preparing variables that are embedded into the query utilizing the proc/sanitizeSQL(var/t) function. This returns an escaped but unquoted SQL-safe string to use.

Example:

var/unsafe_var = input(usr, "Insert some text") as text
var/safe_var = sanitizeSQL(unsafe_var)

var/DBQuery/query = dbcon.NewQuery("INSERT INTO ss13_a_table (some_field) VALUES ('[safe_var]')")
query.Execute()

Note the manual addition of quote marks.

The other method for handling data safety that we have is parameterized queries, which will perform these operations for you. Further explanation in the next paragraph.

Parameterized Queries

Our codebase supports parameterized MySQL statements. The great benefit for them is that all values, parameters, passed into the query will be automatically escaped as the query is prepared. This speeds up development time and makes surface code less complex. Usage of it is pretty simple. First, create a query with parameters embedded in it. Note that all parameters must be delimited with colons : on either side.

Once completed, pass a list of key-value pairs into the DBQuery/proc/Execute() method as the first argument. Note that this list will be morphed during operations, so it is potentially unsafe to reuse. The keys in that list should be the names of the parameters you embedded inside your query, just without the colons this time around. Values can be any DM primitives, or a list of primitives. They will be automatically escaped once the query is composed. Do not escape the query parameters manually! This will result in double-escaping.

Special cases are also handled:

• null is replaced by the MySQL NULL value.
• Lists are composed into MySQL lists: (A, B, C).

A complete example:

var/a = "foo";
var/list/b = list(null, "bar", 1, 4);
var/DBQuery/query = dbcon.NewQuery("SELECT * FROM ss13_a_table WHERE a = :a: AND b IN :b_list:;")
query.Execute(list("a" = a, "b_list" = b))