Guarding against SQL Insertion

[kjtocool]

I am designing two web applications with PHP, and am worried about something attempting to drop my tables, or worse.

 

I know very little about guarding against SQL insertion, I just know that it's a problem.  Any help in this regard would be much appreciated.

[SirChick]

$Username = mysql_real_escape_string($_POST['Username']);

 

 

as long as u got that in bold when getting the value from a form, it should be fairly safe from hackers.

Get a script going and then post it here we can add to it to secure it for ya.

[cooldude832]

$Username = mysql_real_escape_string($_POST['Username']);

 

 

as long as u got that in bold when getting the value from a form, it should be fairly safe from hackers.

Get a script going and then post it here we can add to it to secure it for ya.

 

You can be fairly certain that the string will be properly escaped for entry into your version of mysql, not hacker proof!

 

You never know what a user can toss into a string, you should always verify it isn't a injection string

 

one common issue is with GETvariables

users think its fine to edit the url causing undesired results (By making integer values strings)

 

[kjtocool]

Well, my login validation page looks like so (I also use mysqli functions):

 

<?php
session_start();
?>


<?php
$userName = $_POST["userName"];
$_SESSION['userName'] = $userName;
$passwordHash = md5($_POST["password"]);
$_SESSION['passwordHash'] = $passwordHash;

$databaseConnect = mysqli_connect("localhost", "username", "password", "database")
			Or die("Unable to connect to the database.");

$query = "SELECT user_ID, accessLevel FROM Users WHERE username = '$userName' AND password = '$passwordHash' LIMIT 1";
$result = mysqli_query($databaseConnect, $query);

if (mysqli_num_rows($result) == 0)
	echo "Username or password is incorrect.";
else
{
	$row = mysqli_fetch_assoc($result);
	$_SESSION['userID'] = $row['user_ID'];
	$_SESSION['accessLevel'] = $row['accessLevel'];
	echo '<META HTTP-EQUIV="refresh" CONTENT="0; URL=http://www.xtinctdesigns.com/clients/XtinctCMS/RD1/options.php">';
}

mysqli_free_result($result);
mysqli_close($databaseConnect);
?>

[cooldude832]

one major issue I see is that you are putting that username into the session pre checking it, verify it first and then set the session value

[SirChick]

Well you need the string escape function before $userName is created for the $_POST your crazy if you don't do it.

[kjtocool]

You mean like:

 

<?php
$userName = mysqli_real_escape_string($_POST["userName"]);
?>

 

How do I check the username before storing it as a session variable?

[SirChick]

Like:

 

if($userName == '' ) {
die("username cannot be blank");
}Else{
$_SESSION['userName'] = $userName;
}

 

also check for symbols like :@{}{?><!"$"£%&^^*)¬`\/.,';#[][]-=+_ and remove them from the $userName

[kjtocool]

I do a similar check with javascript before allowing the user to submit:

 

<script type="text/javascript">
function verify()
{
with(document.form1)
{
	var message = "";
	var valid = true;

	if ((userName.value == "")) {
			message += "Please enter your username. \n";
			valid = false;
	}

	if ((password.value == "")) {
			message += "Please enter your password. \n";
			valid = false;
	}

	if (valid == false) {
			alert(message);
			return false;
	}
}
}
</script>

 

Do you recommend re-checking server side with PHP as well?

[SirChick]

I do a similar check with javascript before allowing the user to submit:

 

<script type="text/javascript">
function verify()
{
with(document.form1)
{
	var message = "";
	var valid = true;

	if ((userName.value == "")) {
			message += "Please enter your username. \n";
			valid = false;
	}

	if ((password.value == "")) {
			message += "Please enter your password. \n";
			valid = false;
	}

	if (valid == false) {
			alert(message);
			return false;
	}
}
}
</script>

 

Do you recommend re-checking server side with PHP as well?

 

 

People can turn JavaScript off... so always do the exact same checks with PHP aswell as javascript just encase.

[helraizer]

mysql_real_escape_string is the way to do it. But you could also validate each input it yourself also. For instance the character \ can be harmful to a database so you'd do something like..

 

<?php
if (stristr($_POST['username'], "\\") { //note the backslash has to be "\\" or else it won't work in the way we want it to.
// code to get rid of characters or have the form return false
}
else {
//if no backslash is found use a code to add the username to the database
}
?>

 

As for arrays and code etc.. if the integer in $_GET is supposed to be a number use this :

 

<?php
if(isset($_GET['int']) && is_numeric($_GET['int']) {
//launch code as normal if GET is an integer (numeric)
}
elseif(isset($_GET['int'] && is_array($_GET['int']) {
//code for if GET is an array
}
elseif(isset($_GET['int'] && is_string($_GET['int']) {
//code for GET is a string
}
else {
//code if it's anything else
}

?>

 

Hope that helps,

 

Sam

[kjtocool]

Thanks guys, I'll take a look at your suggestions, do a bit more work and post what I've come up with tomorrow to see if you guys can spot anything I can do better.  Appreciate the help.

[kjtocool]

So, I was working on this an ran into a snag.  My escape_forbidden function isn't working properly.  For whatever reason, the strcspn() function is returning the full number of characters in the string even if a forbidden character is present.

 

Can you see what my error is?

 

<?php
function escape_text($connection, $text) {
	// Stripslashes
	if (get_magic_quotes_gpc()) {
	  $text = stripslashes($text);
	}

	// Escape if not a number
	if (!is_numeric($text)) {
	  $text = mysqli_real_escape_string($connection, $text);
	}

	return $text;
}

function escape_forbidden($text) {
	// Forbidden characters
	$forbidden = ":@{<}{?!$£%&^>*)¬`\/.,;#[][]-=+_";

	// Escape if $text contains forbidden
	if (strlen($text) != strcspn($text, $forbidden)) {
		echo "The input, $text, is invalid.  Please try again.";
		exit;
	}
	else {
		echo strcspn($text, $forbidden);
		return $text;
	}
}

$databaseConnect = mysqli_connect("localhost", "worldofk_admin", "eagles", "worldofk_AdminStuff")
		Or die("Unable to connect to the database.");

$user = escape_text($databaseConnect, $_POST['userName']);
echo $user;
$user = escape_forbidden($user);
echo $user;
?>

 

If I enter "k$jtocool" as the username, it returns:

 

k$jtocool9k$jtocool

[kjtocool]

To answer my own question, the problem seems to be the following characters in $forbidden: ><

 

So now my question becomes, how can I test for these characters?  It doesn't seem to matter where they are in the string, nor if they are the only character.

[rab]

check preg_match()

[kjtocool]

I think there was something else wrong causing the error.  In any case, I got the following working.

 

I would really appreciate input on how strong the below code is against sql insertion and what can be done to improve it.  If you feel I have left out any characters I should test for, or if you can think of any way to improve the code, please let me know. 

 

 

<?php
function escape_text($connection, $text) {
	// Stripslashes
	if (get_magic_quotes_gpc()) {
	  $text = stripslashes($text);
	}

	// Escape if not a number
	if (!is_numeric($text)) {
	  $text = mysqli_real_escape_string($connection, $text);
	}

	return $text;
}

function escape_forbidden($text) {
	// Forbidden characters
	$forbidden = "/!\@#$%^&*():{}?£¬`\/.,;[]-_+=~<>";
	$text = stripslashes($text);

	// Escape if $text contains forbidden
	if (strlen($text) != strcspn($text, $forbidden)) {
		return "invalid";
	}
	else {
		return "valid";
	}
}

$databaseConnect = mysqli_connect("localhost", "username", "password", "database")
		Or die("Unable to connect to the database.");

$user = $_POST['userName'];
$passwordHash = md5($_POST["password"]);

if (escape_forbidden($user) == "invalid") {
	echo '<p align="left" class="style7">The input, ' . $user . ', is invalid.  Please try again.</p>';
	echo '<p align="left" class="style7"><a href="login.php" class="style7">Click here to return to the login screen.</a></p>';
}
else {
	$user = escape_text($databaseConnect, $user);

	$query = "SELECT user_ID, accessLevel FROM Admin_Users WHERE username = '$user' AND password = '$passwordHash' LIMIT 1";
	$result = mysqli_query($databaseConnect, $query);

	if (mysqli_num_rows($result) == 0) {
		echo '<p align="left" class="style7">Username or password is incorrect.</p>';
		echo '<p align="left" class="style7"><a href="login.php" class="style7">Click here to return to the login screen.</a></p>';
	}
	else {
		$row = mysqli_fetch_assoc($result);
		$_SESSION['userName'] = $row['username'];
		$_SESSION['userID'] = $row['user_ID'];
		$_SESSION['accessLevel'] = $row['accessLevel'];
		echo '<META HTTP-EQUIV="refresh" CONTENT="0; URL=http://www.*********.com/CMS/options.php">';
	}

	mysqli_free_result($result);
}

mysqli_close($databaseConnect);
?>