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.

Or just bind the variables itself. — Jaxad0127 22:52, 18 September 2008 (CEST)

Using bind variables in SQL is a common practice and besides the little more effort in coding it has a lot of advantages from the database point of view, not only sql injection. Assume the statement above:

execSql("SELECT field1 FROM table WHERE field2=%s AND field3=%i", value1, value2);

The function will evaluate it with "SELECT field1 FROM table WHERE field2='abc' AND field3='efg'". The next time you run the query maybe it will be "SELECT field1 FROM table WHERE field2='abc' AND field3='xyz'". For the databse, this is a totally new statement. It has to parse it and create an execution plan and check for permission (in case of mysql and pgsql). The costs especially for finding an apropriate execution plan should not be underrated. Using bind variables, the database always gets the statement "SELECT field1 FROM table WHERE field2=? AND field3=?" as the "?" is a known placeholder in SQLite and MySQL. It has to parse the statement only once and can cache the execution plan. I'll present an example from oracle, but it woould be the same for MySQL:

declare 
	v_sql VARCHAR2(100);
begin
	for i in 0 .. 10000 loop
		-- concatination i as string into sql statement
		v_sql := 'SELECT * FROM T WHERE id = ' || to_char(i);
		execute immediate v_sql;
	end loop;
end;
/
Elapsed: 00:00:00.59

declare 
	v_sql VARCHAR2(100);
begin
	for i in 0 .. 10000 loop
		-- using i as bind variable and binding during execution
		v_sql := 'SELECT * FROM T WHERE id = :i';
		execute immediate v_sql using i;
	end loop;
end;
/
Elapsed: 00:00:00.32

The measurable difference: The second statements just takes 54% of the time. IMO we should not surrender using binding techniques. But I agree with Crush, maybe we can simplify the execution using paramarrays and do the magic stuff inside the dataprovider. --Exceptionfault 08:36, 19 September 2008 (CEST)