CODEWITHSUNDEEP
sqlstored-proceduressql-injection
Javier
Javier

Reputation: 4623

Are SQL stored procedures secure?

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

Upvotes: 1

Views: 6537

Answers (5)

kemiller2002
kemiller2002

Reputation: 115488

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.

Upvotes: 5

Vilx-
Vilx-

Reputation: 106912

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.

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 134961

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

Upvotes: 0

Otávio Décio
Otávio Décio

Reputation: 74250

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.

Upvotes: 4

Pontus Gagge
Pontus Gagge

Reputation: 17258

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.

Upvotes: 0

Related Questions