Enfocus Community

Hello,

I was planning on using the standard database connect flow element but concluded that it probably doesn't have any sort of SQL injection prevention built in...Does Switch JavaScript have any sort of SQL injection prevention or a way to sanitize database inputs?

I've gone through the manual on the Statement class and can't find any mention of a function that allows me to build a sanitized query string.

I'm looking for something similar to http://docs.oracle.com/javase/6/docs/ap ... ement.html.

I guess it depends where and how you are using the SQL query. In most cases where I see SQL queries being used in a Switch environment an attempt at injection is irrelevant as the variables used for building the query come from a trusted source (internal user input, or an XML from a web2print system).

If you are in a case where this fear is justified, use stored procedures: instead of using a direct SQL query, you call a stored procedure with one or more parameters. This does not automatically safeguard against an injection attack! You still have to do the right stuff in the stored procedure (https://blogs.msdn.microsoft.com/brian_ ... injection/), but at least it is out of the hands of Switch and in the place where a safeguard against an attack is best placed: in the database itself.

You could of course also use a script expression instead of single-line text with variables when building an SQL query so you can do some basic checking, but the above method surely carries my preference.

I wonder if I can modify my ODBC SQL script to accept a query with something similar to a prepared statement.

Currently, you drop in a query like this: But I think I could allow you to use something like the following: Then in the script, you'd have an option to load parameters. You set parameter :JobNumber => [Job.PrivateData:Key="JobNumber"], and perhaps even set the parameter type as a String. Then, I'll have the script parse the query, strip any unsafe characters from the parameter, insert the parameter, and force it to be a string. Resulting in the following query being sent to ODBC: If you're interested in using switch-sql-query and converting to ODBC, I can work on this enhancement.

Freddy, that looks like it might be the safest way to go, I'll have a look to see whether or not it's viable in my (antiquated) database setup.

Dominick, I'm actually using UPDATE's rather than SELECT's, is this still a viable option?

DtM wrote:Dominick, I'm actually using UPDATE's rather than SELECT's, is this still a viable option?

Yep.

But you'll need an ODBC connection to your database to use my script, so perhaps make sure that you can swing that before jumping ship on stored procedures.

gabrielp wrote:

DtM wrote:Dominick, I'm actually using UPDATE's rather than SELECT's, is this still a viable option?

Yep.

But you'll need an ODBC connection to your database to use my script, so perhaps make sure that you can swing that before jumping ship on stored procedures.

I've got an ODBC connection to the database now, if you're able to get something working that would be great!

DtM wrote:I've got an ODBC connection to the database now, if you're able to get something working that would be great!

It's been added to v2 of the script here: https://github.com/open-automation/switch-sql-query

Currently, it allows for 3 parameters. Just structure your SQL query with named placeholders prefixed by a colon (:jobNumber, :customerName, etc...). It doesn't allow for anonymous placeholders (?, ?).

gabrielp wrote:

DtM wrote:I've got an ODBC connection to the database now, if you're able to get something working that would be great!

It's been added to v2 of the script here: https://github.com/open-automation/switch-sql-query

Currently, it allows for 3 parameters. Just structure your SQL query with named placeholders prefixed by a colon (:jobNumber, :customerName, etc...). It doesn't allow for anonymous placeholders (?, ?).

You are the man! I'll let you know how I get on, thanks Dominick.

So I am not a php dev, but if you're trying to prevent SQL injection the best practice is to utilize prepared, or parameterized, statements. No need to sanitize inputs for SQL injection. If you're sanitizing SQL escape characters from inputs, you're probably doing it wrong.

Also, the mysql_* functions are deprecated in PHP, so you shouldn't be using them in the first place.