Are SQL stored procedures secure?

Question

Are they less vulnerable to SQL injection than doing stuff like mysql_query("SELECT important_data FROM users WHERE password = $password")?

Answer 1

They are more secure than what you are doing. Your query is posting raw SQL to the db which means that your parameters aren't treated as sql parameters but as plain old sql.

Here is what I mean.

With a stored prococedure the password variable can't be sql, it has to be a piece of information the system is looking for. In your example what is actually sent to the db is

SELECT * FROM User where password = ('your password here'--$Password variable).....so someone can do something like

SELECT * FROM user WHERE Password = ('your password here';SELECT * FROM User --$password variable).

or worse yet:

SELECT * FROM user WHERE Password = ('your password here';DROP Database Database_Name --$password variable).

A non-dynamic sql stored procedure won't allow this, because the input parameter won't execute as extra sql.

Parametrized SQL does take care of this, but technically stored procedures are still a little more secure, because the user accessing information in the table doesn't need Read Access. It only needs to be able to execute the stored procedure. Depending on your need this may or may not come into play.

Answer 2

There are no secure technologies. Technologies can only be used in safe or unsafe manners.

That said, stored procedures require slightly more creative coding to permit SQL injection -- which nevertheless happens. Nothing prevents you from concatenating strings in them, in any SQL database engine of which I'm aware.

Answer 3

Not necessarily, you could be doing string concatenation inside of it and the exposure would be the same. If you use dynamic sql with parameter variables (no string concatenation to produce SQL) you'd be fairly well protected.

Answer 4

if they are parameterized correctly and you are not doing dynamic sql then they are more secure and you will also benefit from execution plan reuse

Answer 5

As long as you use parameters and don't use string concatenation for user-entered values, there will be no risk of SQL injection. Stored procedures are a bit "safer" in this aspect, because they encourage you to use parameters. But if in the procedure you do something like

EXECUTE 'SELECT important_data FROM users WHERE password = ' + @password

Then you'll be back to square 1 and very much vulnerable to SQL injections.

Also, there is such a thing as "prepared statements" which are not stored procedures, but can also use parameters. They can thus be used to avoid SQL injection as well.