Protecting against SQL Injections

I heard that using something like this will help protect you against sql injections. Is that right?

When protecting against SQL Injection - worry about 1 character, the Tick (') - sample below:

Left(Trim(Replace(Text,"'","`")),VarCharDefinedLength)

This keeps me protected and makes sure I only insert the max amount of chars that my DB Field will allow

Code (markup):

 

Hi,
this is the most stupid thing I have ever heard about sql injection.
what happens if the parameter is integer?
you should always check numeric values with isnumeric() and encode/decode strings.

 

Zinho -

Interesting... I'm still learning SQL so I'll keep reading and playing with the code until I start to get it.

 

At least it works! The most stupid things are actual problems that plague apps, even commercial ones.

Having said that, this is stupid code to do this. Even if it was a string, I might not want my apostrophes to become backticks, it is not hard to escape them. And this is only for the most basic type of SQL injection.

 

the strongest method: http://ca.php.net/manual/fr/function.mysql-real-escape-string.php

 

i think the best way being protected from sql injections is using a staple cms like JOOMLA

 

believe it or not joomla doesn't do everything and even if it did it would not do it as efficiently as a purpose build application, your best using mysql_real_escape_string with strings and is_numeric or type casting for integers.

 

Maybe suggest something such as a stable database abstraction layer as Joomla and only works for certain types of sites, and even then it does not do particularly well.

 

I think Raiths method is reasonable, though I would suggest the following modification:

replace(text, "'", "''")

Code (markup):

e.g. replace all examples of a SINGLE apostrophe with a DOUBLE apostrophe. This 'escapes' the single apostrophe, preventing it being used as an injection, but still allowing it to be displayed on the screen etc.

As for Zinho's comments about ingegers: yes, true, but that's a discussion on general field verification rather than the specifics of escape-injection for a text field.

I'm sure that the link Zonzon gave us earlier is very helpful - if you speak French.

Meow Purr.

 

sorry for french link!!! here is the english one:
http://ca.php.net/manual/en/function.mysql-real-escape-string.php

never use a simple replace, because you have many many many case that you'll forget! Use the rock solid functions provided and well tested! An example is the use of ASCII or Hexadecimal equivalents caracters that mysql will automatically interpret... I don't have any examples but.... use the provided tools that are proved to work anytime, anywhere!

hope that helps

 

If you're using PHP just use the quote_smart function

function quote_smart($value)
{
	if (get_magic_quotes_gpc()) $value = stripslashes($value);
	if($value == '') $value = ''; //you may want to set this to 'null'
	else if (!is_numeric($value) || $value[0] == '0') $value = "'" . mysql_real_escape_string($value) . "'"; //Quote if not integer
	return $value;
}

Code (markup):

Usage is simply

$sql = "
INSERT INTO
	bunny_user
SET
	email = " . quote_smart($email) . ",
	password = " . quote_smart($pass) . ",
	directory = " . quote_smart($email) . "
";

Code (markup):