SQL Injection

Post Reply
DtM
Member
Posts: 26
Joined: Tue Aug 04, 2015 3:02 pm

SQL Injection

Post by DtM »

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.
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: SQL Injection

Post by freddyp »

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.
User avatar
gabrielp
Advanced member
Posts: 645
Joined: Fri Aug 08, 2014 4:31 pm
Location: Boston
Contact:

Re: SQL Injection

Post by gabrielp »

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:

Code: Select all

SELECT Description FROM OrderHeader WHERE JobNumber = '[Job.PrivateData:Key="JobNumber"]' 
But I think I could allow you to use something like the following:

Code: Select all

SELECT Description FROM OrderHeader WHERE JobNumber = :JobNumber 
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:

Code: Select all

SELECT Description FROM OrderHeader WHERE JobNumber = '123456'
If you're interested in using switch-sql-query and converting to ODBC, I can work on this enhancement.
Free Switch scripts: open-automation @ GitHub
Free Switch apps: open-automation @ Enfocus appstore

Want to hire me? I'm looking for my next gig. Contact me on LinkedIn or via email.
DtM
Member
Posts: 26
Joined: Tue Aug 04, 2015 3:02 pm

Re: SQL Injection

Post by DtM »

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?
User avatar
gabrielp
Advanced member
Posts: 645
Joined: Fri Aug 08, 2014 4:31 pm
Location: Boston
Contact:

Re: SQL Injection

Post by gabrielp »

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.
Free Switch scripts: open-automation @ GitHub
Free Switch apps: open-automation @ Enfocus appstore

Want to hire me? I'm looking for my next gig. Contact me on LinkedIn or via email.
DtM
Member
Posts: 26
Joined: Tue Aug 04, 2015 3:02 pm

Re: SQL Injection

Post by DtM »

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!
User avatar
gabrielp
Advanced member
Posts: 645
Joined: Fri Aug 08, 2014 4:31 pm
Location: Boston
Contact:

Re: SQL Injection

Post by gabrielp »

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 (?, ?).
Free Switch scripts: open-automation @ GitHub
Free Switch apps: open-automation @ Enfocus appstore

Want to hire me? I'm looking for my next gig. Contact me on LinkedIn or via email.
DtM
Member
Posts: 26
Joined: Tue Aug 04, 2015 3:02 pm

Re: SQL Injection

Post by DtM »

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.
katesmith1304
Newbie
Posts: 1
Joined: Tue May 23, 2023 1:56 pm

Re: SQL Injection

Post by katesmith1304 »

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.
Post Reply