Our database administrator insists that wrapping our backend logic in stored procedures will fully block any attempts. Is this claim accurate, or are there hidden risks we should know?
3 answers
Stored procedures do not automatically guarantee safety. If a stored procedure is written using dynamic string concatenation inside its body instead of proper parameterization, it remains completely vulnerable to exploitation. The security benefit only comes when parameters are passed safely into standard SQL statements. If your developers pass unsanitized user input directly into an EXECUTE IMMEDIATE or sp_executesql statement within the procedure, an attacker can still compromise your database just as easily as with dynamic application code.
Could you share a snippet of how one of your complex stored procedures handles input? Seeing whether it utilizes dynamic SQL internally would make it much easier to determine if your database administrator's claim holds water.
They only protect you if implemented correctly without dynamic string building inside the database logic itself. Parameters must be strictly bound to prevent malicious query manipulation.
That is the critical distinction. It is a common myth that simply using a stored procedure acts as a magic shield, but bad coding practices inside the database destroy that protection.
I don't have the exact code snippet right now, but I know we concatenate strings inside a few reporting procedures to build dynamic WHERE clauses based on optional filters chosen by users.