Sql injection attack RRS feed

  • Question

  • Here’s an interesting situation we noticed in a previous clients application that uses dynamic SQL;


    This is a multi tenancy SAAS application that has evolved from an old product.


    They have a reporting component that ensures a client can only select there own data by prefixing every report query with


    SELECT [columns] FROM [TABLENAME] WHERE ClientId=[ClientReference] AND (‘xxxxxx’)


    The ‘xxxx’ represents the filter the client requires on the data in their report.  This is built dynamically by selecting from drop downs for field names.  Text values are freely typed in.  Unfortunately so are parenthesis.


    We were concerned the parentheses were free text so started playing around with queries.  It was discovered the parenthesis are only checked for a matching number of open and closed rather than checking for paired open/close conbinations.


    This lapse allows for an interesting SQL injection attack that was never checked for.  The user can enter;


    EmployeeName=’vvvv’) OR (Salary>0


    Note that This has the same number of matching open/closed parentheses and passes the weak validation.  This leads to the following SQL statement;


    SELECT [columns]


    ClientId = [ClientReference] AND (EmployeeName=’vvvv’) OR (Salary>0)


    Rather then what the programmer intended;


    SELECT [columns]


    ClientId = [ClientReference] AND (EmployeeName=’vvvv’ OR (Salary>0))


    A small but significant difference that demonstates the dangers of dynamic SQL.



    Monday, September 8, 2008 2:04 PM

All replies

  • Hence I hate dynamic SQLs in the application Smile


    I understand for older applications you just need to make a case to migrate out to SSRS or Business Objects or SAS type adhoc reporting rather than providing a way for SQL Injection...  Been there, done that, not anymore...


    Can you implement parameterized query to be sent?  or stored procs? without spending time on SSRS?

    Tuesday, September 9, 2008 1:28 AM