Archive:Executing SQL Statements - Revision history

Jaxad0127: Jaxad0127 moved page Executing SQL Statements to Archive:Executing SQL Statements without leaving a redirect

2013-06-24T19:10:49Z

Jaxad0127 moved page Executing SQL Statements to Archive:Executing SQL Statements without leaving a redirect

← Older revision	Revision as of 19:10, 24 June 2013
(No difference)

Exceptionfault: /* Comments */

2008-09-19T06:36:38Z

Comments

← Older revision		Revision as of 06:36, 19 September 2008
Line 51:		Line 51:

	:Or just bind the variables itself. — [[User:Jaxad0127\|<span style="color: #160196">Jaxad</span>]][[User Talk:Jaxad0127\|<span style="color: #5B038F">0127</span>]] 22:52, 18 September 2008 (CEST)		:Or just bind the variables itself. — [[User:Jaxad0127\|<span style="color: #160196">Jaxad</span>]][[User Talk:Jaxad0127\|<span style="color: #5B038F">0127</span>]] 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. --[[User:Exceptionfault\|Exceptionfault]] 08:36, 19 September 2008 (CEST)

Jaxad0127: /* Comments */ comment

2008-09-18T20:52:02Z

Comments: comment

← Older revision		Revision as of 20:52, 18 September 2008
Line 49:		Line 49:
	execSql("SELECT field1 FROM table WHERE field2=%s AND field3=%i", value1, value2);		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.		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. — [[User:Jaxad0127\|<span style="color: #160196">Jaxad</span>]][[User Talk:Jaxad0127\|<span style="color: #5B038F">0127</span>]] 22:52, 18 September 2008 (CEST)

Crush2: /* Comments */

2008-09-18T19:29:39Z

Comments

← Older revision		Revision as of 19:29, 18 September 2008
Line 47:		Line 47:
	std::string value1="foo";		std::string value1="foo";
	int value2 = 42;		int value2 = 42;
	~~mDb->runQuerry~~("SELECT field1 FROM table WHERE field2=%s AND field3=%i", value1, value2);		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.		The runQuerry function should then automatically take care of escaping any quotes in string arguments and then add quotes around them.

Crush2: counter proposal

2008-09-18T19:28:29Z

counter proposal

← Older revision		Revision as of 19:28, 18 September 2008
Line 42:		Line 42:

	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.		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;
			mDb->runQuerry("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.

Exceptionfault at 11:03, 18 September 2008

2008-09-18T11:03:56Z

New page

{{Category_programming}}

== 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 [http://en.wikipedia.org/wiki/SQL_Injection 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:
# Write your SQL statement containing placeholdes instead of final values
# Let the dataprovider parse and prepare your statement
# Bind the final values to your prepared statement
# 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.