MID-072: Parameterized SQL Queries
Mitigation Tier: Foundational
Description
Web apps should not pass SQL queries for execution by a database unless they conform to one of the recommended query formation mitigations below. Methods can include 1) only executing prepared statements with parameterized queries, 2) only executing stored procedures, and 3) allow-list input validation [1].
Prepared statements with parameterized queries change the way that the web app will process a user request and form a query. When using prepared statements, the web app that processes the user data will take the user data and place it into a pre-defined section of the query, with the rest of the query already formed. Therefore, the actual SQL commands that the database will be executing are handled and compiled before the user input is processed and inserted, so the user input cannot introduce any new potentially malicious commands. This is then coupled with parameterized variables, where variables are set to be a certain type before being inserted into the SQL statement to ensure that no variable can be misconstrued as a command and not a string, for example.
Stored procedures are procedures that are crafted and pre-stored on the web app. They can be sent to the SQL database upon prompting from the client to the web app. Since these statements are pre-crafted and stored before the client has any interaction with the web app, the client cannot send custom queries that may be malicious. Assuming that the user data is parameterized here as well, users will not be able to inject data into the query that will not be interpreted as a literal data type. Therefore, the threat surface is lowered because the stored procedures can be implemented safely, and user input will not be able to add any new commands.
Allow-list input validation is implemented by creating an allow-list of parameters for clients to choose from. This allow list can be implemented through conditionals like if…else and switch statements. This would prevent potential commands contained in user inputs from being a part of the final SQL query statement given to the database.
Limitation: If a device is using stored procedures, particular care needs to be made to what permissions the stored procedure executor has. If the device has device-level users, the user that executes the procedures may need a high-level of permissions, which could make that user a target to threat actors.
IEC 62443 4-2 Mappings
- CR 3.5 - Input validation
References
[1] OWASP. “SQL Injection Prevention Cheat Sheet.” owasp.org. Accessed: Aug. 28, 2024. [Online.] Available: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.htmlLinked