mysql injection help

Hi
If I always use form paramters like this

$param=str_replace("'","",$_GET['param']);

Code (markup):

...........

$query=" ........ where field = '$param' ......";

Code (markup):

Is this enough to prevent injections?

 

Can you please give an example of value for parameter that would break the code? I handle single quotes because I know they are dangerous, but are there any other dangerous characters?

 

But if I ALWAYS use single quotes in mysql queries and I ALWAYS remove them from parameter values, am I safe?

 

Sorry Shawn, but one should NOT be using realEscapeString as that's the same thing as using the old mysql_ functionality. The only legitimate use for that function these days is if you're doing something STUPID like choosing the field name or table name with user input. (a can of worms one that is best left closed)

If you're using mysqli or PDO like a good little doobie and are just passing values to fields, you should be using prepared queries. They eliminate the possibility of injection WITHOUT wasting the time or headaches on what quotes to use or extra functions. (also handy and faster when you want to run the same query multiple times with different data)

mysqli_

// assumes $db is a mysqli object
$statement = $db->prepare('
	SELECT *
	FROM yourTable
	WHERE field = ?
');
$statement->bind_param('s',$_GET['param']);
$statement->execute();

Code (markup):

PDO

// assumes $db is a PDO object
$statement = $db->prepare('
	SELECT *
	FROM yourTable
	WHERE field = :field
');
$statement->bindParam(':field',$_GET['param'],PDO::PARAM_STR);
$statement->execute();

Code (markup):

or if you don't really need typecasting (rarely do you)

// assumes $db is a PDO object
$statement = $db->prepare('
	SELECT *
	FROM yourTable
	WHERE field = :field
');
$statement->execute(array(
	':field' => $_GET['param']
));

Code (markup):

The latter is often handy as you can often build your form structure to let you easily copy the data over in one fell swoop as an array when you have multiple values.

I prefer PDO over mysqli since it means you can target more than just mySQL, the ability to pass an array, and the ability to use names for your array indexes instead of just a question mark.

Prepared queries are also nice in that because they are static, when you use them with PDO you can build a name indexed array of queries, loading different arrays based on what SQL engine you are running -- be it mySQL, postGre, msSQL, oracle, whatever.

Though if you're still using the old mysql_ functions, ignoring the big red box saying don't use that anymore, enjoy your vulnerabilities.

 

I used to think that way -- baby steps and so forth -- but it's just bad practice to let people continue to use old/outdated methods when the modern techniques are no more difficult, often easier and simpler if you can get past the conceptual hurdles, and fix so many long standing issues. I realized some time ago it helps nobody in the long term to slap the rose coloured glasses on people's heads and let them continue to be led down the garden path.

Too bad there's so much web-rot and shelf-rot on the subject, making such changes an uphill fight. But again it's why I consider everything IT related, including knowledge, to have a shelf life of three years. Three years is obsolete, five years is the scrap heap. (making 4 year educational programs a complete waste of time). If you're not ripping your own code from just a year or two ago to shreds, you're in the wrong business.

Or as I've said many times, the day you think there's nothing new to learn, is the day the rest of the world leaves you behind.

 

Thank you for advice. Now I have a point to start the documentation and testing. Anyway, I hope this topic is not finishing here. I have to modify about 100.000 code lines, and want to do this only one time.

 

100.000 lines of code? and you wondering if you prevent injection with only one line of code? did you write the code yourself?

 

Which is one reason I'm here. I stopped working in February, preparatory to selling my house and moving out of state. If I hadn't written or looked at any code since then I'd be at least a generation behind. (And some of the questions here make me write enough code to actually run, so I'm keeping up mentally.)