Protecting against SQL Injection:

[Crew-Portal]

I was wondering what most of you guys use to prevent against SQL injection? This is what I am currently using.

function transform_HTML($string, $length = NULL){
	$string = trim($string);
	$string = utf8_decode($string);
	$string = htmlentities($string, ENT_NOQUOTES);
	$string = str_replace("\"", """, $string);
	$string = str_replace("#", "#", $string);
	$string = str_replace("$", "$", $string);
	$string = str_replace("%", "%", $string);
	$string = str_replace("&", "&", $string);
	$string = str_replace("'", "'", $string);
	$string = str_replace("(", "(", $string);
	$string = str_replace(")", ")", $string);
	$string = str_replace("*", "*", $string);
	$string = str_replace("+", "+", $string);
	$string = str_replace(",", ",", $string);
	$string = str_replace("-", "-", $string);
	$string = str_replace("/", "/", $string);
	$string = str_replace(":", ":", $string);
	$string = str_replace(";", ";", $string);
	$string = str_replace("<", "<", $string);
	$string = str_replace("=", "=", $string);
	$string = str_replace(">", ">", $string);
	$string = str_replace("?", "?", $string);
	$string = str_replace("@", "@", $string);
	$string = str_replace("[", "[", $string);
	$string = str_replace("]", "]", $string);
	$string = str_replace("^", "^", $string);
	$string = str_replace("_", "_", $string);
	$string = str_replace("`", "`", $string);
	$string = str_replace("{", "{", $string);
	$string = str_replace("|", "|", $string);
	$string = str_replace("}", "}", $string);
	$string = str_replace("~", "~", $string);
	
	$length = intval($length);
	if ($length > 0){
		$string = substr($string, 0, $length);
	}
	return $string;
}

Which then gets called by:

if ($action == 'login'){ // Login Action
$_SESSION['loginerror'] = FALSE;
$myusername = transform_HTML($_POST['login-username'], 21);
$mypassword = transform_HTML($_POST['login-password'], 21);

$sql="SELECT * FROM $table[users] WHERE username='$myusername' and password=MD5('$mypassword')";
$result=mysqli_query($db, $sql);
// Mysql_num_row is counting table row
$count=mysqli_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Login Stuff
}

Is there a more efficient way, or more secure way of doing this?

[Psycho]

Um, no. That doesn't do anything to prevent SQL injection since most SQL injection is due to the use of quote marks in the content. You shouldn't create your own solution anyway. Either use the built-in functions of the platform you are using or use prepared statements.

 

With mysqli_ extention you can use mysqli_real_escape_string or you can use prepared statement. Prepared statements are the best option, but take a little more time to learn. It is beyond the capability to try and instruct you in the use of prepared statements in a forum post. But, there are plenty of tutorials available. Here is the manual for it though: http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

 

FYI: You should never modify a password using something like that. Any time you modify a password you risk making it less secure. You should only need to hash it

Edited March 18, 2014 by Psycho

[Crew-Portal]

Um, no. That doesn't do anything to prevent SQL injection since most SQL injection is due to the use of quote marks in the content.

 

I understand that however the quot marks dont execute because the string below strips them to ASCII, I tried injecting myself and it doesnt appear to work, however removing the lines below from the code allowed me to do so.

$string = str_replace("\"", """, $string);
$string = str_replace("'", "'", $string);
$string = str_replace("`", "`", $string);

Isn't that how it works?

[Psycho]

The fact that you may have built a custom function to prevent some SQL Injections doesn't mean you should do that. There are already built-in functions and/or processes to prevent SQL Injection which you should use. I also don't know why you are escaping so many different values. It looks like you are trying to escape the data for output to an HTML page more so than to prevent SQL Injection. Those are two different things and should be implemented separately. If you escape content for HTML purposes before storing int he database you lose the ability to use the data for other purposes.

 

You should store the data in the database exactly as the user submitted it - only escaping to prevent SQL Injection using the appropriate functions/processes that already exist. Then, when you are outputting the data you should escape as needed based upon the mode of output: HTML, XML, etc. Again, you should use the appropriate escape functions such as htmlentities() to accomplish that.