The best way to prevent SQL injection

[johnsmith153]

I want to ensure no SQL attacks and the entire process of passing/receiving from the dbase is secure. All the security with quote, double quote stuff scares the hell out of me.

 

I also here stories in a mysql table where people can do things like add:

== droptable

to the end of your script and it deletes the table or something.

 

I fully understand how people can do SQL injection, just need advice on what is the best way I should prevent it:

 

My current method (not coded yet, planning first) removes a lot of useability, but I think it will make security very good.

 

Submitting data to database: (assuming for a message board)

1. Reject if characters entered other than A-Z a-z 0-9 ! . ; , ’ : ) ? £ @ + - = # ^ (i.e cant enter ")

2. use str_replace("\'", "'", $name); to replace the ' with \'  (escape it but my method may be a little different)

And obviously reverse it before displaying again.

I cant use the mysql_real_escape_string() as I am using Caspio Bridge Web Services and not MySQL for this project. A database web services API.

I wont use Magic Quotes, addslashes / stripslashes  as this is deprecated from php 6

 

htmlentities??

[NorthWestSimulations]

Te hehehe...  I just made a post like this, Try:

 

<?php
function secure_globals($string, $length){
	$string = trim($string);
	$string = utf8_decode($string);
	$string = htmlentities($string, ENT_NOQUOTES);
	$string = str_replace("#", "&#35", $string);
	$string = str_replace("%", "&#37", $string);
	$string = mysql_real_escape_string($string);
	$string = htmlspecialchars($string);
	$length = intval($length);
		if ($length > 0){
			$string = substr($string, 0, $length);
		}
	return $string;
}
?>

 

and then all variables that the user could possibly edit are stored as (for example):

<?php
$page = secure_globals($_GET['page'], 32);
$action = secure_globals($_GET['action'], 32);
$user = secure_globals($_POST['username'], 16);
$pass = secure_globals($_POST['password'], 32);
?>

[johnsmith153]

I was just looking and thinking how crap my method is compared to yours. I'll probably get hacked 10 times then.

[NorthWestSimulations]

Bah you can just copy my code!