OWASP
Update: SQL_Injection_Prevention_Cheat_Sheet - SQL Injection
https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
What is missing or needs to be updated?
(https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html#defense-option-4-escaping-all-user-supplied-input)
Defense Option1 and Defense Option 2 are not enough to prevent SQL injection . In addition to option 1 or option 2 option 4 is required.
Defense Option 4: Escaping All User-Supplied Input[¶] This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injections in all situations.
How should this be resolved?
Defense Option 4: Escaping All User-Supplied Input¶ This technique must be used along with Option 1 and Option 2 . Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injections in all situations however it may not always be practically feasible as end user(s) often require special characters
We need to explicitly mention Option 4 is also required as part of Option 1 and Option 2.
We have followed the usage of parametrized queries or sql stored procedure and still found application open to SQL injection related attacks. Here is an example how
SQL Server sample - We had also seen a similar issue with postgress also
create table test(name nvarchar(2000))
create procedure proc_test (@p nvarchar(2000))
as
begin
update test
set name = @p
end
declare @p1 nvarchar(2000)
set @p1= ''+@@version+''
exec proc_test @p1
select * from test
In the sample above the sql version gets stored in column test can be retrieved .
Later during further analysis I have out while performing Option 1: Use of Prepared Statements (with Parameterized Queries) Option 2: Use of Properly Constructed Stored Procedures The client libraries internally perform Option 4: Escaping user supplied input. So when the code above is integrated with C# it will not be susceptible to SQL injection as the client libraries escape the user input . So no longer propose a change.
Let me know your thoughts.
I just submitted a major re-write of the SQL Injection cheatsheet where we heavily discouraged manual escaping. Can you review that please?
