Protecting Against Ad-Hoc Query Exploits

Ad-hoc queries are temporary SQL queries created and executed to perform a specific task without prior preparation or to save as permanent procedures or functions. Usually, they can be used to analyze data, search for particular information, or solve temporary tasks. However, if user-defined input data generates the text in ad-hoc queries dynamically, they may be vulnerable to SQL injections.

In the article, we’ll explore what ad-hoc queries are and what security and access risks they may cause. We’ll also examine some techniques that might help protect databases when using ad-hoc queries.

Contents:

• Understanding ad-hoc queries
• Security risks of ad-hoc queries
• SQL injection via ad-hoc queries
• Protection techniques

Understanding ad-hoc queries

What is an ad-hoc query? It is a SQL statement a user creates to retrieve or analyze data quickly. The query is not pre-written or stored in the database, and the user runs it once in response to a spontaneous situation.

Key characteristics of ad-hoc queries are as follows:

• Spontaneous: They are written when required.
• Temporary: Usually, they are not saved for repetitive cases.
• Specific: They are designed to respond to a particular case or solve a specific problem.

All this allows ad-hoc queries to be flexible and adapted to different needs, which makes them a perfect tool for database developers and analysts.

Let us consider the following scenario: An account manager receives a complaint from a VIP client about delayed shipments for the last month. Therefore, the manager needs to investigate this issue quickly and analyze delayed shipments for a VIP client without a pre-defined report.

We have created a sample database – CustomerSupportAnalytics. It contains SQL sample tables – Customers, SalesOrderHeader, SalesOrderDetail, and Products populated with the test data specific to the scenario. To analyze this data, execute the following SELECT query:

SELECT 
    soh.SalesOrderID,
    soh.OrderDate,
    soh.DueDate,
    soh.ShipDate,
    soh.TotalAmount,
    c.CustomerName
FROM 
    SalesOrderHeader soh
JOIN 
    Customers c ON soh.CustomerID = c.CustomerID
WHERE 
    c.IsVIP = 1 -- Only VIP customers
    AND soh.ShipDate > soh.DueDate -- Late shipments
    AND soh.OrderDate >= DATEADD(DAY, -30, GETDATE()); -- Last 30 days

The query filters out the VIP customers from the Customers table and identifies shipments that were delayed by checking where the shipping date is later than the due date. In addition, it looks for the orders placed within the last 30 days.

As you can see, the query returned the following rows based on the sample data:

• SalesOrderID 101: For John Doe (VIP), shipped late on 2024-11-10 when the due date was on 2024-11-07
• SalesOrderID 103: For ACME Corp (VIP), shipped late on 2024-11-25 while the due date was on 2024-11-20

So, the query greatly helped in this urgent situation. The account manager received the required data quickly without the need to generate a report for data analysis beforehand.

Security risks of ad-hoc queries

As ad-hoc queries are usually executed without predefined structure or validation, they might come with potential security risks and expose databases to malicious exploits. The risks may include the following:

• Vulnerability to SQL injection attacks: SQL injection occurs when an attacker manipulates the user-defined input to execute malicious SQL code.
• Revealing sensitive data: This may happen unintentionally as ad-hoc queries can be easily modified and executed by users.
• Lack of access control. Ad-hoc queries bypass the structured querying mechanisms, such as using predefined views or parameterized queries, which usually offer better security controls. Therefore, this allows users to execute potentially dangerous operations, such as deleting data, altering tables, or running resource-heavy queries that might impact database performance.
• Lack of auditing mechanisms. Since ad-hoc queries are executed in real time without logging options, it is difficult to detect and mitigate the attack, which might exist in queries, promptly.
• Increased database load and performance issues: Complex ad-hoc queries may overload the database and consume a lot of resources that will result in poor performance.

SQL injection via ad-hoc queries

SQL injection occurs when users try to insert SQL statements into a query to execute them in the database. Since ad-hoc queries are often created on the fly based on user input, which is not checked, they can be vulnerable to SQL injection attacks.

Let us explore how SQL injection attacks can exploit ad-hoc queries.

Login bypass

SQL injection can allow attackers to break into systems by bypassing login pages. This happens when a login query takes user input directly without proper validation or parameterization. For example, adding OR '1'='1' to a login query can let an attacker bypass authentication checks.

Consider the following example to check credentials:

SELECT * FROM Users WHERE Username = 'user_input' AND Password = 'user_password';

If the attacker modifies the SELECT statement as follows:

SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = 'anything';

The query returns all rows from the Users table and grants access to the attacker without valid credentials because '1'='1' is always true.

Data exfiltration

Attackers exploit vulnerabilities in ad-hoc queries to get sensitive information, such as usernames, passwords, credit card numbers, or other personal data. By injecting malicious SQL code into a vulnerable query, they can manipulate the database to reveal private information.

For example, a search box on the website uses an ad-hoc query to look up product details:

SELECT ProductName, Price FROM Products WHERE ProductName LIKE '%user_input%';

Using an injection like:

' UNION SELECT username, password FROM Users -- 

Will make the resulting query as follows:

SELECT ProductName, Price FROM Products WHERE ProductName LIKE '%' UNION SELECT username, password FROM Users -- %';

In the output, the query will provide the unauthorized disclosure of a list of user credentials.

Database structure discovery

SQL injection can be used to uncover the database structure, such as table names, column names, and relationships, to plan further targeted attacks.

For example, if an attacker uses the following SQL injection:

' UNION SELECT table_name, NULL FROM information_schema.tables -- 

The query will fetch a list of all available table names from the information_schema.tables system view, which stores metadata about all tables in the database.

Data modification or deletion

This type of SQL injection lets attackers alter, corrupt, or delete data from the database. It can disrupt business operations, compromise data integrity, and impact availability.

For example, a web application has a vulnerable input field to update user information:

UPDATE Users SET Email = 'user_email' WHERE UserID = 'user_input';

If an attacker injects user_input as:

1234; DROP TABLE Users; --

The resulting query becomes as follows:

UPDATE Users SET Email = 'user_email' WHERE UserID = 1234; DROP TABLE Users; -- 

It will execute the DROP TABLE command and delete the Users table.

Application logic bypass

This type of SQL injection allows attackers to alter the expected workflow or logic of an application. By injecting malicious SQL code, they might adjust prices, skip restrictions, or give themselves higher permissions, such as admin access.

For example, an application uses the following query to update user details:

UPDATE Users SET Email = 'user_email' WHERE Username = 'user_input';

If the attacker enters the following as a username:

attacker'; UPDATE Users SET Role = 'admin' WHERE Username = 'attacker'; --

The query changes to:

UPDATE Users SET Email = 'new_email' WHERE Username = 'attacker'; UPDATE Users SET Role = 'admin' WHERE Username = 'attacker'; --

As a result, the query will elevate the attackers’ privileges and grant them administrator access.

Second-order SQL injection

Second-order SQL injection arises when attackers inject malicious code into a database, where it is stored at first. Later, the malicious code gets executed when the stored data is used in another SQL query. This type of attack is dangerous because it’s harder to detect and prevent since it does not happen immediately.

For example, an attacker registers the username as follows:

John'; DROP TABLE Users; --

The username is stored in the database without any harm. Later, the username is used in a different SQL query:

SELECT * FROM Orders WHERE Username = 'John'; DROP TABLE Users; --';

So, when the query is run, the DROP TABLE Users part is also executed, and the table is deleted.

Protection techniques

Since SQL injection is one of the most widely spread threats to which ad-hoc queries are vulnerable, the following protection methods may prevent the database from data loss and malicious operations.

Use parameterized queries

Unlike dynamic SQL queries that directly insert user inputs, parameterized queries, also known as prepared statements, allow using placeholders instead of a user-defined value. When a parameterized query is executed, the database recognizes user input as plain text, regardless of what the user inserts in the query.

For example, you can easily create parameterized queries using dbForge Query Builder for SQL Server. It is an advanced tool to build SELECT, INSERT, UPDATE, and DELETE queries of any complexity on a visual diagram without requiring code knowledge.

To begin, download the tool and install it on your computer. Once done, open dbForge Query Builder.

To build a parameterized query, follow the steps:

1. On the SQL toolbar, select New Query to open the Query Builder diagram.

2. From Database Explorer, drag the required table to add it to the diagram.

3. Select the checkboxes next to the columns you want to add to the query. The columns to be added will also be shown on the Selection tab of the bottom Tabbed Editor.

4. To add a parameter to the query, do the following:

• Navigate to the Where tab of the Tabbed Editor.
• Select the plus icon – Add a new condition to this group.

• In the new WHERE syntax construction that opens, select enter a value on the left and double-click the column name under the Table list. To close the list, select Close.

• Select enter a value on the right and declare a parameter placeholder using @ followed by a parameter name, for example –@JobTitle . Based on this parameter, the data will be filtered.

5. To assign a value to the parameter, do the following:

• At the bottom of the Tabbed Editor, switch to the Text view and select Execute on the SQL toolbar.

When you try to run the query, the tool will prompt you to enter a value for the parameter.

• In the Edit Parameters window that opens, specify the value and select OK to save the changes.

The query will return only the rows filtered by the parameter value.

As you can see, this approach is perfect for users who want to quickly create secure and flexible queries without writing complex SQL syntax.

Employ stored procedures

Another protection technique will be using stored procedures. They are pre-written SQL scripts stored and executed on the database server. They help isolate database logic from the code on the server. When using with parameters they reduce the risk of SQL injection and limit direct access to database tables.

Consider the following backend query string:

string query = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";

If username is set to ' OR '1'='1, the query changes to:

SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '';

Since 1 is equal to 1, the query will always return rows regardless of whether the username or password is correct. So, the query could allow unauthorized access.

To prevent this, it is recommended to use stored procedures. These pre-defined SQL scripts include placeholders for parameters, allowing data to be securely passed into them at runtime.

So, to solve this issue, create the procedure with the declared parameters for Username and Password:

CREATE PROCEDURE AuthenticateUser
    @Username NVARCHAR(50),
    @Password NVARCHAR(50)
AS
BEGIN
    SELECT * 
    FROM Users 
    WHERE Username = @Username AND Password = @Password;
END;

Then, call the procedure:

cursor.execute("EXEC AuthenticateUser @Username=?, @Password=?", (username, password))

where:

• AuthenticateUser is the stored procedure name.
• @Username=? and @Password=? are placeholders that correspond to the parameters defined in the stored procedure. The ? acts as a placeholder that will be replaced by actual values passed in the cursor.execute() method.
• (username, password) are user-defined values for the username and password fields respectively.

In the result, the database treats the username and password as data and not as executable SQL.

Validate all user inputs

Input validation involves checking all data provided by users to ensure it conforms to expected formats, data types, and value ranges before processing it in SQL queries. Here is a list of tips to validate all user inputs:

• Perform client-side and server-side validation to minimize the risk of unexpected behavior or security issues.
• Avoid special characters, such as ', ;, or --, which are often used in SQL injection attacks, unless they are explicitly required.
• Use parameterized queries for an added layer of security.

Implement least privilege access

This method ensures that database accounts have minimum permissions to perform specific tasks.

When you limit permissions, an attacker who wants to get access to the database through SQL injection is restricted to the actions allowed for that account. For example, a read-only account prevents unauthorized modifications or deletions of data. The recommended best practices include but are not limited to:

• Assign roles based on tasks, such as read-only for reporting tools and write access only for data entry operations.
• Avoid using high-privilege accounts, such as dbo or root, for regular tasks.
• Regularly review and update permissions to ensure users have access to what they really need.

Secure CRUD operations 

CRUD, which stands for Create, Read, Update, and Delete, refers to basic SQL operations used to interact with a database. If they are not secured, attackers can exploit vulnerabilities to steal sensitive data, alter records, or even delete critical information in the database.

Therefore, it is important to protect these operations from threats, such as unauthorized data access, data manipulation or removal:

• Use parameterized queries to prevent SQL injection.
• Check all user-defined input values to ensure they are in the correct format and within expected ranges before processing them.
• Restrict who can perform a CRUD operation. For example, only admins can delete records, while users can update their data in the database.
• Keep track of all CRUD operations for audit purposes and for detecting unauthorized activity.
• Wrap complex updates in transactions to ensure data consistency.

dbForge Studio for SQL Server and dbForge SQL Complete can be great assistants in creating CRUD statements.

SQL Complete and dbForge Studio are the ultimate tools for SQL Server database developers and administrators. SQL Complete enhances productivity with the intelligent code completion, auto-suggestions, syntax highlighting, and built-in code snippets that allow users to write errorless queries faster. It also features smart filtering and a SQL formatter for consistent code style, while its T-SQL Debugger helps monitor the runtime behavior of database objects and locate logic errors.

Meanwhile, dbForge Studio offers an all-in-one solution for database management, design, and development. It provides a visual query builder, data and schema comparison, debugging tools, data import/export functionality, code completion capabilities, query profiler, etc. By combining these tools, developers and DBAs can automate tasks, optimize queries, ensure database security and integrity, and boost productivity.

Create CRUD statements

dbForge Studio and dbForge SQL Complete allow easily generating customizable and reusable CRUD scripts.

dbForge SQL Complete: In Object Explorer, right-click the database object for which you want to generate a CRUD script and select SQL Complete > Script Table as CRUD.

The generated script will open in a new SQL document:

In addition, it is possible to configure options to add the CRUD procedures. For more information about how to configure the general options for CRUD operations, see CRUD General options.

dbForge Studio: In Database Explorer, right-click the database object for which you want to generate a CRUD script and select Generate Script As > CRUD and select whether you want to generate the script to a new SQL document, save it to a file, or copy it to the clipboard.

The script will look as follows:

For more information about how to customize the CRUD templates, see How to generate the DDL and DML statements for database objects.

Moreover, dbForge Studio has a professional administration tool – Security Manager – to protect SQL Server databases. The tool allows managing user roles, permissions, and access control settings. With the Security Manager, you can easily create and modify logins, users, application roles, schemas, assign and manage permissions, grant or revoke user privileges and access rights.

Conclusion

Ad-hoc queries are common practice for managers, developers, or analysts when they have to investigate urgent or specific data requests without the need to use pre-defined analysis or reporting tools. Still, they have security risks, which should be taken into account when running ad-hoc queries.

In the article, we got deeper in risks that may arise with ad-hoc queries. We also examined best practices to protect database data from SQL injection, one of the main vulnerabilities of ad-hoc queries.

Download the best tools for SQL Server design, development, and management and evaluate all the features it provides within a free 30-day trial period.