Archive:Executing SQL Statements

This article contains information for Programmers working or interested in working for The Mana World

Executing SQL Statements from C++

The current Data access layer (DAL) of tmwserv provides an abstract interface to execute SQL statements against several types of database engines. SQL statement can be executed using 2 different methods whereas the second one is the preferred way.

using execSQL method

The easiest way to execute SQL statement is to call the execSql method, providing the statement as complete string. As this looks very simple and comes as a one-liner, you have to be aware of SQL injection attacks.

const RecordSet& execSql(const std::string& sql, const bool refresh = false)
const RecordSet& info = mDb->execSql("SELECT x FROM y WHERE z = 'a'");

Using prepared Statements

The secure way in executing sql, without having to worry about sql injection attacks, is to use prepared statements and bind variables. Many database systems are even faster when using them, because the internal memory can be used much more efficiently. To execute a statement using bind variables, you have to do the following steps:

1. Write your SQL statement containing placeholdes instead of final values
2. Let the dataprovider parse and prepare your statement
3. Bind the final values to your prepared statement
4. Execute the prepared statement with your bound values
   • Return to 3, if you have to execute the statement more than once, but with different values
   • Finalize the prepared statement and free memory

Ported to our DAL this means the following:

std::string sql = "SELECT x FROM y WHERE z = ?";   // ? is used as placeholder for out final value
                                                   // column or table names can never be expressed as placeholders!
mDb->prepareStatement(sql, 1);                     // prepare the statement, define that we need 1 bind variable
mDb->bindParameter("a", 1);                        // bind value "a" as first parameter
const RecordSet& info = mDb->execStatement();      // execute statement

As you can see, it takes a little more effort to execute a SQL statement using bind variables, but that should not frighten you doing it that way! The execStatement() methods frees up memory for you and finalizes the prepared statement. If you want to reexecute it with different bind variables have a look at the following example:

std::string sql = "INSERT INTO x (z) VALUES (?);";
mDb->prepareStatement(sql, 1);

for (int i = 1; i <= 10; i++)
{
    mDb->bindParameter(i, 1);
    mDb->execStatement((i==10));
}

Calling execStatement(bool) with false, resets the bound variables in the prepared statement to NULL but keeps the statement in prepared state. Only if we come to the last execution, we want to finalize and clean up everything.

Comments

How about using a syntax similar to the printf function from the standard library instead? This would make the code more readable and less cumbersome to code than having a bindParameter call for every variable in the querry, in my opinion.

std::string value1="foo";
int value2 = 42;
execSql("SELECT field1 FROM table WHERE field2=%s AND field3=%i", value1, value2);

The runQuerry function should then automatically take care of escaping any quotes in string arguments and then add quotes around them.