Filtering SQL Results Using a Dynamic List of Values

When working with databases, it’s common to need to filter results based on specific criteria. In this article, we’ll explore how to dynamically return all SQL results where the value of one column equals the value of another column.

Understanding the Problem

The problem presented is that of filtering search results based on a dynamic list of values. The user signs into the search form with their EmployeeNumber, and if it matches other SupEmp numbers, they want to see all rows that match their EmployeeNumber.

Let’s break down the current code and understand what’s missing:

var vendorCols = "SupEmp, FIRST_NAME, LAST_NAME, EmployeeNumber, DEPARTMENT, Position, Status";

$('#EmployeeLookup').autocomplete({
    source: function (request, response) {
        var sql = "SELECT " + vendorCols + " FROM EMPLOYEE_SEARCH" // Note the missing conditions here!
            + " WHERE UPPER(FIRST_NAME + LAST_NAME) like '%" + request.term.toUpperCase() + "%'";

        $.ajax({
            url: '/public/GetData',
            type: 'POST',
            data: JSON.stringify({ query: sql, connectionName: "LawsonConnection" }),
            contentType: "application/json; charset=utf-8",
            dataType: 'json',
            success: function (dataArray) {
                response($.map(dataArray, function (item) {
                    if (request.term.indexOf("'") &gt;= 0) { item = 'Do not enter special characters' }
                    if (item === "Data Not Found")
                        item = "Employee Cannot Be Found";
                    return {
                        label: item
                    };
                }));
            }
        });
    },
});

Identifying the Issue

The current code is filtering by the first name and last name, but it’s not taking into account the dynamic list of SupEmp numbers. We need to modify the SQL query to include an AND clause that checks if the EmployeeNumber matches any value in the SupEmpNumbers list.

Modifying the SQL Query

To achieve this, we’ll add an additional condition to the SQL query using the IN operator. The IN operator is used to check if a value exists within a set of values.

var sql = "SELECT * FROM EMPLOYEE_SEARCH" // Note the changes!
    + " WHERE UPPER(FIRST_NAME + LAST_NAME) like '%" + request.term.toUpperCase() + "%'"
    + " AND EmployeeNumber IN (" +request.SupEmpNumbers.join(', ') + ")";

However, we need to be cautious when using the IN operator with user input. If the request.term is not properly sanitized, it could lead to SQL injection attacks.

Sanitizing User Input

To prevent SQL injection attacks, we should ensure that the request.term is properly sanitized before using it in our query.

var sql = "SELECT * FROM EMPLOYEE_SEARCH"
    + " WHERE UPPER(FIRST_NAME + LAST_NAME) like '%" + request.term.toUpperCase() + "%'"
    + " AND EmployeeNumber IN (" +request.SupEmpNumbers.map(function (num) {
        return "'" + num + "'";
    }).join(', ') + ")";

Using Parameterized Queries

However, the above solution still poses a risk if an attacker manages to manipulate the SQL query. A better approach would be to use parameterized queries instead of string concatenation.

var sql = "SELECT * FROM EMPLOYEE_SEARCH"
    + " WHERE UPPER(FIRST_NAME + LAST_NAME) like ?"
    + " AND EmployeeNumber IN (" +request.SupEmpNumbers.map(function (num) {
        return "'" + num + "'";
    }).join(', ') + ")";

$.ajax({
    url: '/public/GetData',
    type: 'POST',
    data: JSON.stringify({ query: sql, params: [request.term.toUpperCase(), request.SupEmpNumbers] }),
    contentType: "application/json; charset=utf-8",
    dataType: 'json',
    success: function (dataArray) {
        // ...
    }
});

Using an ORM

If you’re using an Object-Relational Mapping (ORM) library, it can help to prevent SQL injection attacks and simplify your queries.

var sql = "SELECT * FROM EMPLOYEE_SEARCH"
    + " WHERE UPPER(FIRST_NAME + LAST_NAME) like :term"
    + " AND EmployeeNumber IN (:supEmpNumbers)";

$.ajax({
    url: '/public/GetData',
    type: 'POST',
    data: JSON.stringify({ query: sql, params: { term: request.term.toUpperCase(), supEmpNumbers: request.SupEmpNumbers } }),
    contentType: "application/json; charset=utf-8",
    dataType: 'json',
    success: function (dataArray) {
        // ...
    }
});

Conclusion

In this article, we explored how to dynamically return all SQL results where the value of one column equals the value of another column. We discussed the importance of sanitizing user input and using parameterized queries or ORM libraries to prevent SQL injection attacks.

By following these guidelines, you can write more secure and efficient code that meets your database requirements. Remember to always prioritize security when working with databases and user input.

Last modified on 2024-02-25