Jiwa

Hi All,

We have received this from the customer from their Azure DB recommendation


This specific query relates to the inventory search but it applies to the JiwaSearch. I can see that Jiwa search replace ' with ''

I'm not expert in SQL injection and not sure what else can be done to harm the database?
Obviously all users are running under JiwaUser login which isn't the dbowner, it cant create new db login/user (especially not on Azure DB). So it cannot gain access to other databases
Jiwauser can potentially delete/truncate all data but I really not sure how?

Hi Perry,

The search window is still quite legacy in structure and so we don't use paramaterised queries for the SQL. As such, tooling to identify things like SQL injection vulnerabilities will be triggered when they see non-parametised queries - but in this case it's fairly safe because all the search window queries are in the context of a server side cursor, and all the user entered text which has the quotation mark ' replaced with two quotations - preventing escaping out of the literal and into SQL.

So, to illustrate I'm going to try a SQL injection attack by putting in some specially crafted text in the search window. If you follow the same steps as I show you'll see how SQL Injection doesn't seem possible via the search window.

First I'll create a test product with PartNo 'test' - I'm going to try to use SQL injection later to delete this part - so this new part is going to be our sacrificial canary - if it disappears then the injection attack was successful - if it doesn't then the attack failed.



Next I'll open the search window and enter my injection text - I want to delete part 'test', so my text I enter in a field is going to be:

Code: Select all

'; DELETE FROM IN_Main Where PartNo = 'test'

At this point if I examine the SQL in the "Show SQL" dialog of the search window, we see the query as follows:



You can already see the text has the quotation marks replaced with two quotation marks, meaning my injection text remains a literal and has not "escaped" into becoming a SQL command.

If you ran profiler at the same time to capture the SQL, it would look like the following:



No surprises there - it's taken the SQL and made it a literal string being passed to sp_cursorOpen - which also has to replace all single quotations with two single quotations.

In conclusion I cannot find a way to allow an SQL injection attack - as we're handling the quotations correctly. It is recommended best practice to use paramaterised queries to prevent injection - which is why tools like the Azure vulnerability assessment tools are triggered by those queries - and we do plan to give the search window a jolly good rewrite someday which will eliminate any concerns (asynchronous queries using TOP and OFFSET - doing away with cursors - and using the TSQL Parser to build queries is something I've tinkered with and would be ideal).

Hi Mike

Thanks for your quick response.

My sql injection hacking skill is really limited to using '; escape so that was the only thing I have tested myself.

We have a client on Azure SQL where we have seen those warnings also, which amount to nothing from the application itself i.e. it's internal users.

For another ERP we work with that can't handle parameterised queries, don't even get me started on that subject, I use the below function, where information can come from an external source, I can't remember where I got it from but it does the job pretty well.

Code: Select all

  public static string Sanitize(this string stringValue)
        {
            if (null == stringValue)
                return stringValue;

            stringValue = stringValue.RegexReplace("-{2,}", "-");                 // transforms multiple --- in - use to comment in sql scripts
            stringValue = stringValue.RegexReplace(@"[*/]+", string.Empty);      // removes / and * used also to comment in sql scripts
            stringValue = stringValue.RegexReplace(@"(;|\s)(exec|execute|select|insert|update|delete|create|alter|drop|rename|truncate|backup|restore)\s", string.Empty, RegexOptions.IgnoreCase);
            return stringValue;
        }