Is it true that stored procedures will prevent databases from being injected? I did a little research and I found out that SQL-Server, Oracle and MySQL are not safe against SQL injections if we only use stored procedures. However, this problem does not exist in PostgreSQL. Does stored procedure implementation in PostgreSQL's core prevent it from SQL injection or are there any other reasons/differences? Can I use SQL injection in PostgreSQL if we only use stored procedure?
In short - no, stored procedures does not prevent from SQL Injection. It depends on how you do handle dynamic SQL inside stored procedure.
For more complete answer, check what is posted here: https://stackoverflow.com/questions/627918/am-i-safe-against-sql-injection
Check these links and the picture will get clearer:
"...Measures to avoid SQL injection
- Validate all input coming from the user on the server.
- Avoid the use of dynamic SQL queries if there an alternate method is available.
- Use parameterized stored procedure with embedded parameters.
- Execute stored procedures using a safe interface such as Callable statements in JDBC or CommandObject in ADO.
- Use a low privileged account to run the database.
- Give proper roles and privileges to the stored procedure being used in the applications."
IF you use SP correctly, then you are pretty much safe from SQL Injection (assuming you still do proper input validation anyway, of course). IF.
Ah, but what does it mean, to use SP correctly?
Two of the most common mis-uses of SP that I see often, and can each lead to SQL Injection even with Stored Procedures, are:
- Dynamic SQL inside the SP.
- Calling the SP from code as you would execute a concatenated string. I.e. just sending to the database "exec spname (param1)", instead of using command/parameter objects (or parameterizing the query, depending on language). SQLi can still be injected into the SP call...
You need variable binding along with named parameters, regardless if the query is a stored procedure or not. Also need to worry about certain SQL statements like: