Replies To: SQL Queries - code/parametrized stored procedures

Re: SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 11:57 AM

By using just stored procedures, you can prevent a lot of trouble. However, if you still echo user input richt into the procedure call, you can get into a lot more trouble. Users can still attack using SQL injection (ending your query and starting a new one), with an operator thinking he completely guarded by using just stored procedures.

For every security issue: think everywhere. All levels of coding can and will be abused. You can protect yourself on one level, if you leave another completely open, you're still as vulnerable.

Re: SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 12:35 PM

Hary, on 17 Feb, 2009 - 01:57 PM, said:

However, if you still echo user input richt into the procedure call, you can get into a lot more trouble. Users can still attack using SQL injection (ending your query and starting a new one), with an operator thinking he completely guarded by using just stored procedures.

I thought, and was explained to me, that when you pass variables as a parameter to the stored procedure, it treats those parameters for what they are. So if you pass some malicious code as the parameter and it's going into a varchar field then it treats that as a string/varchar and does not execute the code.

Re: SQL Queries - code/parametrized stored procedures

As long as you feed it in a Varchar and you do not care about escaping, it all can go bad.

_very_ simple example

mysql_query("my_proc(" . $_POST['a'] . ")");

What if a bad user posts "); DROP DATABASE; my_proc(""

Now, this results in _two_ queries, where just one is to your procedure, with the other one the bad user can do whatever he wants. Much sophisticate hacks exists ofcourse...
Main thing: do _never_ think you are safe and fixed al of it.

Re: SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 12:51 PM

That is not correct, using Stored Procedures is a good start, but they can still be injected. Always, and I mean always validate and clean input that is received from the user. A good thing to remember is to treat all user input as malicious, and act accordingly. IT's a good idea to create a small little function that cleans user input instead of having to write it over and over again.