SQL Queries - code/parametrized stored procedures

Which route to take to prevent sql injection

Page 1 of 1

4 Replies - 2061 Views - Last Post: 17 February 2009 - 12:51 PM Rate Topic: -----

#1 Vomster  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 198
  • Joined: 15-May 08

SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 06:52 AM

I was told that by using parametrized stored procedures is a way to prevent sql injection as opposed to running the input through code to "clean" it.

Should you strictly rely on the stored procedures or would you code for it or use both?

I've never written any code for the public but I'm about too and I'm trying to make sure I understand how to protect the database.
Is This A Good Question/Topic? 0
  • +

Replies To: SQL Queries - code/parametrized stored procedures

#2 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

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.
Was This Post Helpful? 0
  • +
  • -

#3 Vomster  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 198
  • Joined: 15-May 08

Re: SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 12:35 PM

View PostHary, 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.

This post has been edited by Vomster: 17 February 2009 - 12:36 PM

Was This Post Helpful? 0
  • +
  • -

#4 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: SQL Queries - code/parametrized stored procedures

Posted 17 February 2009 - 12:44 PM

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.

This post has been edited by Hary: 17 February 2009 - 12:45 PM

Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1632
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

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.

Here's a small function I use for cleaning user input

function clean_input($input)
{
   $input = trim($input);
   
   //check to see if magic quotes are turned on
   if(get_magic_quotes_gpc())
   {   
	  $input = stripslashes($input);
   }
   
   //check for numeric values, if not
   //clean it
   if(!is_numeric($input))
   {
		$input = mysql_escape_string($input);
   }
   return($input);
} 


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1