Kevin McGahey - May 18, 2023
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 Risks of Hand-Coding a REST API
When hand-coding a REST API, developers must diligently implement secure coding practices to mitigate SQL injection risks. However, human error or oversight can inadvertently leave vulnerabilities in the code, making the API susceptible to attacks. Some common risks associated with hand-coding a REST API include:
- Insufficient input validation: Inadequate input validation can allow malicious SQL statements to be executed, compromising the security of the underlying database.
- Concatenation of SQL queries: Improper concatenation of user inputs directly into SQL queries can lead to SQL injection vulnerabilities. If the inputs are not properly sanitized or parameterized, an attacker can manipulate the query structure and inject malicious code.
- Lack of output encoding: Failing to properly encode output data can expose the API to attacks such as cross-site scripting (XSS), where an attacker injects malicious scripts into the API’s responses.
Preventing SQL Injection Attacks with DreamFactory
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:
Default Use of Parameterized Queries
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.
Built-in Input Validation and Endpoint Encoding
DreamFactory offers robust input validation mechanisms that validate user inputs against predefined rules, such as data type, length, or regular expressions. This helps ensure that only valid and expected data is processed, preventing SQL injection attempts.
Another great feature is secure endpoint configuration. DreamFactory enables developers to define strict access controls, including authentication and authorization mechanisms, at the API endpoint level. This ensures that only authorized users can access and interact with the API, reducing the risk of unauthorized data access.
Also, DreamFactory automatically applies output encoding techniques, such as HTML entity encoding, to prevent cross-site scripting attacks. This ensures that any user-supplied data included in API responses is properly encoded, rendering it harmless to client applications.
Support for Stored Procedures
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.
Support for Least Privilege Communication
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.
Conclusion
SQL injection attacks are a serious security threat to web applications that use SQL databases. Hand-coding a REST API requires a deep understanding of SQL and web security best practices to prevent SQL injection attacks. DreamFactory provides an easier and more secure way to create REST APIs. DreamFactory automates the process of securing the API, provides built-in security features, and uses prepared statements to ensure that user input is separated from SQL commands, reducing the risk of SQL injection attacks. By using DreamFactory, developers 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.
Related Reading:
https://blog.dreamfactory.com/direct-database-connectors-risks/
Kevin McGahey is an accomplished solutions engineer and product lead with expertise in API generation, microservices, and legacy system modernization, as demonstrated by his successful track record of facilitating the modernization of legacy databases for numerous public sector organizations.