by Jason Gilmore • June 23, 2021
A SQL injection attack is an attempt to modify the syntax of a SQL query executed in conjunction with a user-initiated action within a software application. Some examples of user-initiated actions include searching for a product within an e-commerce catalog, adding a comment to a blog post, and creating a new user account. In each of these scenarios, the user uses a form to submit input (e.g. a search term, email address, etc.), and that input is ultimately passed to a SQL query, often by way of an API call.
For instance, when creating a new user account, the user might complete and submit a web form that includes fields for the user’s name, email address, and desired password. Upon submitting that form, an API call is made to the application’s backend code, and a query such as this is generated:
INSERT INTO users VALUES(NULL, 'Bob Smith' '[email protected]', 'RwmjhwioDlJE');
In a perfect world filled with legitimate users, there wouldn’t be any issue with passing this data directly into the SQL statement. However, in reality doing so would be a recipe for disaster because it exposes the application to a SQL injection attack. Imagine instead of inserting a legitimate password into the registration form, a malicious user enters this:
asdf'); DELETE FROM users; --
Lacking any validation and other safeguards, the SQL query will now look like this:
INSERT INTO users VALUES(NULL, 'Bob Smith', '[email protected]', 'asdf'); DELETE FROM users; --
If this query actually manages to execute and the application database authorization privileges are improperly configured (a very common oversight), then there is a very real chance that the users table has been completely deleted.
The DreamFactory platform prevents SQL injection attacks by adhering to the defense principles laid forth by the OWASP SQL Injection Prevention Cheat Sheet. The Open Web Application Security Project (OWASP) is a representative gold standard with regards to the research and dissemination of best practices pertaining to web security. DreamFactory’s adherence to these best practices can be summarized as follows:
All input passed to the destination database via a generated API is treated as a contiguous string, meaning potentially dangerous characters such as ‘ and ; are not subject to interpretation. This means the dangerous input presented in the previous example won’t be parseable as a second query (DELETE FROM users). Instead, the input asdf'); DELETE FROM users; -- will be inserted as the password parameter, complete with spaces and special characters. While it certainly isn’t a typical password, we can be rest assured that the attacker won’t accomplish his goal of damaging the table data.
DELETE FROM users
input asdf'); DELETE FROM users; --
Many IT organizations, including particularly those who have adopted Microsoft SQL Server as the preferred database vendor, often require most if not all interactions with the database occur by way of a stored procedure. A stored procedure consists of a set of SQL statements that when called can be executed as a contiguous block of logic. Because SQL syntax is far more rich than that which is most often used to for instance select data from a table, it’s possible to for instance require input to be of a certain data type. Many organizations prefer this approach because it provides a well-defined set of requirements for interacting with the database, disallowing for instance direct interaction with a table or view.
DreamFactory treats stored procedures just like tables and views in that stored procedures can be accessed via dedicated API endpoints. DreamFactory also supports the ability to pass input to the stored procedures, and return responses to the client. Thanks to this support, IT organizations are able to take advantage of DreamFactory’s ability to instantly generate database APIs without shirking compliance requirements.
Least privilege communication ensures the client can successfully complete delegated tasks without any possibility of overreach into areas that could expose sensitive information or cause damage to the database. In the context of database access, this means creating a database user capable of executing the desired queries while possessing the minimum required privileges to do so. This database user can then be provided to DreamFactory when generating the database API. Subsequent use of that API will then be limited to those privileges associated with the proxy database user.
Equally important to satisfying the goal of least privilege communication is DreamFactory’s powerful role-based access control (RBAC) manager. This point-and-click interface helps administrators to further restrict an API’s capabilities by limiting which endpoints are otherwise available by default to the proxy database user. For instance, an administrator could create one RBAC definition that ensures API access is read-only and is limited only to a small subset of all available tables, views and stored procedures. Another RBAC definition might only expose a few stored procedures and nothing more. Finally, a third RBAC definition might use row-level filtering to restrict access only to data associated with EU- or US-based entities.
DreamFactory’s extensive SQL injection prevention safeguards means your IT team can focus more time on adding customer-focused value to your applications, and far less time fretting over time-consuming and often error-riddled code.
Join the DreamFactory newsletter list.