CODEWITHSUNDEEP
mysqlsqlsql-injection
user4237435
user4237435

Reputation: 353

are stored procedures really secure against sql injections

I need to convince someone that he needs to sanitize the user input in addition to the user of stored procedures. well I know I sound crazy but I do not feel comfortable enough with store procedures only. My first reason is that I am able to cause errors in the stored procedure but because of the fact that the application itself handles errors such that error messages are coded it is difficult for outside to understand the what there are. but I still think that this is not secure.

Does any one has a suggestion ? or am I wrong to doubt stored procedures?

Upvotes: 2

Views: 413

Answers (3)

Guffa
Guffa

Reputation: 700272

Using stored procedures normally protects against SQL injection, but is not the only solution to prevent SQL injections, and it doesn't protect against all forms of SQL injection.

It's not the stored procedure itself that makes the big difference, but parameterised queries, which is the most common way to call a stored procedure. By putting the values used by the query in parameters, you let the database library handle them instead of having to escape them correctly yourself.

It's possible to write code that is safe against SQL injections without using parameterised queries, but it's difficult. You have to know exactly what characters you need to escape in a string for the specific database that you are using, and if you get it wrong you are pretty much as unprotected as if you didn't know about SQL injections at all.

If you use parameterised queries, then the step of sending the values into the database is safe from SQL injection, but the query itself might not be. If the query generates and executes SQL code itself, you have the same problem with escaping strings correctly. It's however not so usual to create SQL code in the SQL code, and if you do it you are very aware of that you are doing it.

Upvotes: 0

overflowed
overflowed

Reputation: 1838

No it's not safe on it's own. You can also do in a stored procedure something like this:

SET @sql = 'Select * from products where name like ''' +@spinput+''' ';
exec(@sql);

With the wrong value in @spinput you can inject code.

However you can write stored procedures that are safe against sql injection.

Upvotes: 2

user3175748
user3175748

Reputation:

Even if you use proper parameters, you can still mess with the database. You could insert a script that goes in as a parameter, but when it's displayed on a web page starts doing something it shouldn't. Use parameters to ensure your database is used as intended, but also sanitize the output later - never trust user-entered data.

Upvotes: 1

Related Questions