Guarding Against SQL Injection in Ballerina Applications 🛡️

This article was written using Ballerina Swan Lake Update 8 (2201.8.0).

In the realm of database-backed programs, one of the foremost security concerns is SQL Injection. This perilous scenario unfolds when a dynamic SQL query incorporates user inputs.

To shield against SQL Injections, Ballerina employs the Prepared Statement method.

Check this out:

sql:ParameterizedQuery = `SELECT * FROM users WHERE user_name=${username};`

Tip: User inputs are seamlessly inserted into the query with ${...}.

Now, let’s break down how this query is executed:

1. Template the Query: The query starts as SELECT * FROM users WHERE user_name=${username}. The sql module swaps the "${}" placeholders with “?”. All insertions are treated as user inputs – no surprise attacks here!
2. Compile the Query: The database double-checks the SQL query for syntax and semantics, making sure everything is legit. The server gives it a thumbs-up for execution.
3. Placeholder Replacement: User data values are sent to the database, swapping placeholders with real values. Since the query is pre-compiled, no matter what values are added, it won’t turn a SELECT into a DELETE. Take that, SQL Injection!
4. Query Execution: Boom! The processed query runs, and we get the results we want.

Now, in the Ballerina universe, some devs ask:

Why can't we template the table name in the query? 🤔
Why not add order by key as placeholder? 🤔

Hold up! Table names and order by keys are like VIPs in SQL queries. They gotta be there in Step 2 for a smooth compilation. Thats why, sql:ParameterizedQuery only allows parameters in the query; the rest gotta stay put.

But hey, does this mean we can’t jazz up a query dynamically in Ballerina? Nah, we can, but it’s not a walk in the park. Our advice? Play it safe with an allow list when you’re crafting dynamic queries in code.

SQL identifiers you toss into the query better be on the allow list. Any user input going off-script? Boom! Throw an error. Check this Ballerina snippet:

sql:ParameterizedQuery orderUsersPartialQuery = `SELECT * FROM users ORDER BY `;
sql:ParameterizedQuery orderUsers = ``;
string orderByKey = "";
match orderByKey {
    "username" => {
        orderUsers = sql:queryConcat(orderUsersPartialQuery, `username`);
    }
    "age" => {
        orderUsers = sql:queryConcat(orderUsersPartialQuery, `age`);
    }
    _ => {
        return error("Invalid order by key");
    }
}

Keep rocking those queries, and say no to SQL Injection! 🎸✨