SQL Prepared Statements

OL Connect Professional
1

We are taking some data from an XML file and inserting it into a database, following this example: Workflow - Import XML Data into MS SQL Database - Workflow - Upland OL User community (more or less).

However, this allows for SQL Injection, which is Bad™. We have added a SearchAndReplace action to escape single quotes ('). However, to do this at the most sensible moment means doing it for a dozen or so variables. Besides, the proper way to prevent SQL injection is to use prepared statements anyway.

How do we use prepared statements?

2

Excerpt from this article:

To execute a stored procedure, you use the CALL statement:

CALL stored_procedure_name(argument_list);

In this syntax, you specify the name of the stored procedure after the CALL keyword. If the stored procedure has parameters, you need to pass arguments inside parentheses following the stored procedure name.

3

Stored procedures are something completely different from prepared statements (which are the same as parametrized statements).

An example of a prepared statement would be:

INSERT INTO products (name, price) VALUES (?, ?);

And then the two values are sent to the DBMS separate from the query. Because the values aren’t a part of the query, support in the interface to the database is required. It’s also because of this that SQL Injection isn’t possible, and that’s why they are industry standard for making sure SQL Injection doesn’t happen.

4

You’ll need to use a script for prepared statements. Something like the following:

var conn = new ActiveXObject("ADODB.Connection");
conn.ConnectionString="DSN=myDSN;SERVER=127.0.0.1;UID=myUserName;PWD=myPassword;DATABASE=myDB;PORT=3306;";
conn.Open();

var sqlCommand=new ActiveXObject("ADODB.command");
sqlCommand.ActiveConnection = conn;
sqlCommand.CommandText = 'SELECT * FROM Customers where gender=?';
sqlCommand.Prepared = true;

var myParam = sqlCommand.CreateParameter("genderParam",200,1,32,"male");
sqlCommand.Parameters.Append(myParam);

var recSet = sqlCommand.Execute();

while(!recSet.EOF){
    Watch.Log(recSet("Gender"),2);
    recSet.MoveNext;
}
5

Thanks for the answer. We’ll see if we should use this way or if we’re better off just sticking with the manual escaping.

It’s a bit disappointing that the “native” support for SQL only supports the use of a method that has serious security implications (part of the number one on the OWASP Top 10) even though there has been a standard way to avoid this that has been standard practice across the industry for over a decade.