Sql-server – SQL Injection penn testing from the queries only

Securitysql serversql-injection

Is there an established method or tool available to perform pen testing on an application by only testing queries it sends to the database?

For example, if I have a bunch of SQL Servers hosting various websites and a query came through that wasn't parameterised, is there a way I can detect these?

Example query that probably isn't secure:

SELECT x,y,z FROM logins WHERE username = 'xx' and password = 'yyy'

…instead I would expect a secure application to be probably be using sp_executesql.

Best Answer

Parameters are passed as an RPC (remote procedure call) to SQL Server over the TDS protocol. Consequently, a parameterized query will show as RPC starting/completed events in a trace (SQL Trace or Extended Events). Depending on the API and application methods used, parameterized queries might call API system procedures (e.g. sp_prepare) instead of sp_executesql or may call user stored procedures directly.

Ad-hoc queries are passed as a batch one or more SQL statements. These will show as a batch starting/completed events in a trace. Ad-hoc queries can be a SQL injection risk when:

the query is not static or
it built in the application code with string concatenation from untrusted sources.

Static queries might contain literals too so one would need to guess at the context and examine the app code to determine if the query is a vulnerability.

Be aware that even parameterized queries can pose an injection risk if built dynamically with a mix of parameters and literals, or by concatenation from untrusted sources. I can't think of a way to identify such practices without examining the app code or finding the vulnerability in penn testing.

Related Solutions

Postgresql – Do stored procedures prevent SQL injection

No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection:

This sql server code:

CREATE PROCEDURE [dbo].[sp_colunmName2]   
    @columnName as nvarchar(30),
    @type as nvarchar(30), 
    @searchText as nvarchar(30)           
AS
BEGIN
    DECLARE @SQLStatement NVARCHAR(4000)
    BEGIN
        SELECT @SQLStatement = 'select * from Stations where ' 
            + @columnName + ' ' + @type + ' ' + '''' + @searchText + '''' 
        EXEC(@SQLStatement)
    END      
END
GO

roughly equivalent to postgres:

CREATE or replace FUNCTION public.sp_colunmName2 (
    columnName  varchar(30),
    type varchar(30), 
    searchText  varchar(30) ) RETURNS SETOF stations LANGUAGE plpgsql            
AS
$$
DECLARE SQLStatement VARCHAR(4000);
BEGIN
    SQLStatement = 'select * from Stations where ' 
            || columnName || ' ' || type || ' ' || ''''|| searchText || '''';
    RETURN QUERY EXECUTE  SQLStatement;
END
$$;

The developer's idea was to create a versatile search procedure, but the result is that the WHERE clause can contain anything the user wants, allowing a visit from little Bobby Tables.

Whether you use SQL statements or stored procedure doesn't matter. What matters is whether your SQL uses parameters or concatenated strings. Parameters prevent SQL injection; concatenated strings allow SQL injection.

Mysql – How to protect MySQL database from sql-injection

you better use prepared statement from your programming source code, e.g. for PHP use PDO's prepare statement!

Check this - https://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php

Best Answer

Related Solutions

Postgresql – Do stored procedures prevent SQL injection

Mysql – How to protect MySQL database from sql-injection

Related Question