Mysql_real_escape_string HELP

[Tenaciousmug]

I am having huge trouble with this Mysql_real_escape_string to prevent SQL Injection. I have tried everywhere possible to input it in my code. My code looks a lot different than most peoples. I mean my login/registration system works PERFECT.. besides that it's not protected from SQL Injection yet which is why I'm trying to secure it.

$cxn = mysqli_connect($host,$user,$password,$database) or die("Query died: connect");
$sql = "SELECT username FROM Member WHERE username='$_POST[fusername]'";
$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
$num = mysqli_num_rows($result);

Okay now I know you input it in username=... after that. But I tried everything with the stupid quotation and single marks and I just cant' seem to get it right. I hope some genius can come along and help me. xD

Also, how do you prevent SQL Injection from the url? How they can delete your whole Member database by putting something in the url after .php?id= something. How do you prevent that?

Thanks for all this information. After this, I will be completely satisfied and can start moving on further with my site. Thank you!

[CFROG]

Just throw the username into a variable and escape it that way.

$username = mysql_real_escape_string($_POST['fusername']);
$cxn = mysqli_connect($host,$user,$password,$database) or die("Query died: connect");
$sql = "SELECT username FROM Member WHERE username='$username'";
$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
$num = mysqli_num_rows($result);

There are some functions to help with preventing SQL injection but thinking ahead and being smart about your design will also help prevent injection.

Here is a good page that discusses not just ways to escape your data but also how to design your site with preventing SQL injection in mind.

http://www.learnphponline.com/security/sql-injection-prevention-mysql-php

[Tenaciousmug]

Yeah that's what I was doing, but whenever I do this, this error pops up:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'asterock'@'localhost' (using password: NO) in /home2/asterock/public_html/login.php on line 7

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home2/asterock/public_html/login.php on line 7

And this is line 7:
$fusername = mysql_real_escape_string($_POST);

This is how the whole first case looks now:

<?php
session_start();
switch (@$_POST['Button'])
{
case "Log in";
include("haha.php");
$fusername = mysql_real_escape_string($_POST['fusername']);
$cxn = mysqli_connect($host,$user,$password,$database) or die("Query died: connect");
$sql = "SELECT username FROM Member WHERE username='$fusername'";
$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
$num = mysqli_num_rows($result);
if($num > 0)
//username was found
{
$fpassword = mysql_real_escape_string($_POST['fpassword']);
$sql = "SELECT username FROM Member WHERE username='$fusername' AND password=md5('$fpassword')";
$result2 = mysqli_query($cxn,$sql) or die("Query died: fpassword");
$num2 = mysqli_num_rows($result2);
if($num > 0) //password matches
{
$_SESSION['auth']="yes";
$_SESSION['username'] = $_POST['fusername'];
$sql = "INSERT INTO Login (username,loginTime) VALUES ('$_SESSION[username]',NOW())";
$result = mysqli_query($cxn,$sql) or die("Query died: insert");
header("Location: testing.php");
}
else
{
$message_1="The username, '$_POST[fusername]' exists. However you have not entered the correct password! Please try again.";
$fusername=strip_tags(trim($_POST['fusername']));
include("login_form.php");
}
}
else // username was not found
{
$message_1 = "The username you entered does not exist! Please try again.";
include("login_form.php");
}
break;

[CFROG]

The mysql_real_escape_string function is dependent on SQL therefore a connection to your SQL server is needed. It's not the string that is throwing the error, it's your connection to the database. Make sure a connection is established before invoking the function.

[Tenaciousmug]

The mysql_real_escape_string function is dependent on SQL therefore a connection to your SQL server is needed. It's not the string that is throwing the error, it's your connection to the database. Make sure a connection is established before invoking the function.

But I include the document that gives the connection. Before I mess with those Mysql_real_escape_string functions, my connection is fine and I am able to login.. but I am also able to login with the password x=x.. so I'm trying to fix that, but.. that shouldn't make my connection fail.
How do I establish that connection then if it is already working?

[CFROG]

I need to ask just to be sure ... are you calling your include for the db connection BEFORE the escape_string function? Put your include at the very top of the page if it's not already.

<?PHP

include(dbc.php);

[Tenaciousmug]

I need to ask just to be sure ... are you calling your include for the db connection BEFORE the escape_string function? Put your include at the very top of the page if it's not already.

<?PHP

include(dbc.php);

Yes.

<?php
session_start();
switch (@$_POST['Button'])
{
case "Log in";
include("haha.php");
$fusername = mysql_real_escape_string($_POST['fusername']);
$cxn = mysqli_connect($host,$user,$password,$database) or die("Query died: connect");
$sql = "SELECT username FROM Member WHERE username='$fusername'";
$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
$num = mysqli_num_rows($result);
if($num > 0)
//username was found

Just a little top preview of my code. After that case "Log in";
It includes that haha.php which is my connection to the database.
And then the CXN variable starts it up.
But I even tried putting the fusername variable underneath the $cxn and it still won't work.

[rajarajan2017]

you should make the connection before using the mysql_real_escape_string otherwise it utilized the last connection link.

[Tenaciousmug]

you should make the connection before using the mysql_real_escape_string otherwise it utilized the last connection link.

Yes, I know. I did mention that I posted the $fusername variable after every line to see if it made a difference. It didn't. I put it after the $cxn, then after the $sql, then after the $result, and then after the $num and still.. the same error.

[rajarajan2017]

whats the error shown?

[Tenaciousmug]

whats the error shown?

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'asterock'@'localhost' (using password: NO) in /home2/asterock/public_html/login.php on line 8

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home2/asterock/public_html/login.php on line 8

I know. It's not establishing that connection, but before I use the Mysql_real_escape_string, my connection is fine and I can log in. But I want to fix the SQL Injection problem.

Here is my whole PHP code if anyone knows how to help:

<?php
session_start();
switch (@$_POST['Button'])
{
	case "Log in";
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database) or die("Query died: connect");
	$fusername = mysql_real_escape_string($_POST['fusername']);
	$sql = "SELECT username FROM Member WHERE username='$fusername'";
	$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
	$num = mysqli_num_rows($result);
	if($num > 0)
	//username was found
	{
		$fpassword = mysql_real_escape_string($_POST['fpassword']);
		$sql = "SELECT username FROM Member WHERE username='$fusername' AND password=md5('$fpassword')";
		$result2 = mysqli_query($cxn,$sql) or die("Query died: fpassword");
		$num2 = mysqli_num_rows($result2);
		if($num > 0) //password matches
		{
			$_SESSION['auth']="yes";
			$_SESSION['username'] = $_POST['fusername'];
			$sql = "INSERT INTO Login (username,loginTime) VALUES ('$_SESSION[username]',NOW())";
			$result = mysqli_query($cxn,$sql) or die("Query died: insert");
			header("Location: testing.php");
		}
		else
		{
			$message_1="The username, '$_POST[fusername]' exists. However you have not entered the correct password! Please try again.";
			$fusername=strip_tags(trim($_POST['fusername']));
			include("login_form.php");
		}
	}
	else // username was not found
	{
		$message_1 = "The username you entered does not exist! Please try again.";
		include("login_form.php");
	}
	break;

	case "Register":
	/* Check for blanks */
	foreach($_POST as $field => $value)
	{
		if(empty($value))
		{
			$blanks[] = $field;
		}
		else
		{
			$good_data[$field] = strip_tags(trim($value));
		}
	}
	if(isset($blanks))
	{
		$message_2 = "The following fields are blank. Please enter the required information: ";
		foreach($blanks as $value)
		{
		$message_2 .="$value, ";
		}
		extract($good_data);
		include("login_form.php");
		exit();
	}
	/* validate data */
	foreach($_POST as $field => $value)
	{
		if(!empty($value))
		{
			if(preg_match("/name/i",$field) and !preg_match("/user/i",$field) and !preg_match("/log/i",$field))
			{
				if(!preg_match("/^[A-Za-z' -]{1,50}$/",$value))
				{
					$errors[] = "$value is not a valid name. ";
				}
			}
			if(preg_match("/email/i",$field))
			{
				if(!preg_match("/^.+@.+\\..+$/",$value))
				{
					$errors[]="$value is not a valid email address.";
				}
			}
		} // end if not empty
	}
	foreach($_POST as $field => $value)
	{
		$$field = strip_tags(trim($value));
	}
	if(@is_array($errors))
	{
		$message_2 = "";
		foreach($errors as $value)
		{
			$message_2 .= $value." Please try again";
		}
		include("login_form.php");
		exit();
	} //end if errors are found

	/* check to see if username already exists */
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database) or die("Couldn't connect to server");
	$sql = "SELECT username FROM Member WHERE username='$username'";
	$result = mysqli_query($cxn,$sql) or die("Query died: username.");
	$num = mysqli_num_rows($result);
	if($num > 0)
	{
		$message_2 = "$username already exists. Select another username.";
		include("login_form.php");
		exit();
	} // end if username already exists
	else // add new member to database
	{
		$sql = "INSERT INTO Member (username,createDate,password,firstName,email) VALUES ('$username',NOW(),md5('$password'),'$firstName','$email')";
		mysqli_query($cxn,$sql);
		$_SESSION['auth']="yes";
		$_SESSION['username'] = $username;
		header("Location: testing.php");
	}
	break;

	default:
	include("login_form.php");
}
?>

[Tenaciousmug]

Oh and this is how Haha.php looks:

<?php
$host="******";
$user="********";
$password="**********";
$database="********";
?>

Am I doing that right since it says my connection isn't there?..

[JRM]

Mysqli has it's own special PHP methods. I think you are getting an error because you are out of scope.
mysqli

The fix may be as simple as:

$fusername = mysqli_real_escape_string($_POST['fusername']);

Add the i onto all instances of mysql_real_escape_string

Hope that was it.

[Tenaciousmug]

Mysqli has it's own special PHP methods. I think you are getting an error because you are out of scope.
mysqli

The fix may be as simple as:

$fusername = mysqli_real_escape_string($_POST['fusername']);

Add the i onto all instances of mysql_real_escape_string

Hope that was it.

It got my connection to work. xD But here is the error message it is showing now:

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in /home2/asterock/public_html/login.php on line 8

Here is line 8:

$fusername = mysqli_real_escape_string($_POST['fusername']);

I know it only has one parameter, but what do I put for the second?.. It doesn't need a second. xD

[CFROG]

Your first parameter is your connection, the second parameter is the string you are trying to escape:

mysqli_real_escape_string($connection, $fusername);

I'm not 100% on what your connection needs to be, try $cxn I believe that may do the trick.

[Tenaciousmug]

Your first parameter is your connection, the second parameter is the string you are trying to escape:

mysqli_real_escape_string($connection, $fusername);

I'm not 100% on what your connection needs to be, try $cxn I believe that may do the trick.

Well it's working perfectly, but I can still sign in with x=x as my password... I thought that was suppose to fix it..

[JRM]

see this:
mysqli.real-escape-string

You might want to use the object version

[Tenaciousmug]

see this:
mysqli.real-escape-string

You might want to use the object version

I replaced it with that one and it's still letting me log in with x=x as the password :(

<?php
session_start();
switch (@$_POST['Button'])
{
	case "Log in";
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database);
	$fusername = $cxn->real_escape_string($_POST['fusername']);
	$sql = "SELECT username FROM Member WHERE username='$fusername'";
	$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
	$num = mysqli_num_rows($result);
	if($num > 0)
	//username was found
	{
		$fpassword = $cxn->real_escape_string($_POST['fpassword']);
		$sql = "SELECT username FROM Member WHERE username='$fusername' AND password=md5('$fpassword')";
		$result2 = mysqli_query($cxn,$sql) or die("Query died: fpassword");
		$num2 = mysqli_num_rows($result2);
		if($num > 0) //password matches
		{
			$_SESSION['auth']="yes";
			$_SESSION['username'] = $fusername;
			$sql = "INSERT INTO Login (username,loginTime) VALUES ('$fusername',NOW())";
			$result = mysqli_query($cxn,$sql) or die("Query died: insert");
			header("Location: testing.php");
		}
		else
		{
			$message_1="The username, '$fusername' exists. However you have not entered the correct password! Please try again.";
			$fusername=strip_tags(trim($fusername));
			include("login_form.php");
		}
	}
	else // username was not found
	{
		$message_1 = "The username you entered does not exist! Please try again.";
		include("login_form.php");
	}
	break;

	case "Register":
	/* Check for blanks */
	foreach($_POST as $field => $value)
	{
		if(empty($value))
		{
			$blanks[] = $field;
		}
		else
		{
			$good_data[$field] = strip_tags(trim($value));
		}
	}
	if(isset($blanks))
	{
		$message_2 = "The following fields are blank. Please enter the required information: ";
		foreach($blanks as $value)
		{
		$message_2 .="$value, ";
		}
		extract($good_data);
		include("login_form.php");
		exit();
	}
	/* validate data */
	foreach($_POST as $field => $value)
	{
		if(!empty($value))
		{
			if(preg_match("/name/i",$field) and !preg_match("/user/i",$field) and !preg_match("/log/i",$field))
			{
				if(!preg_match("/^[A-Za-z' -]{1,50}$/",$value))
				{
					$errors[] = "$value is not a valid name. ";
				}
			}
			if(preg_match("/email/i",$field))
			{
				if(!preg_match("/^.+@.+\\..+$/",$value))
				{
					$errors[]="$value is not a valid email address.";
				}
			}
		} // end if not empty
	}
	foreach($_POST as $field => $value)
	{
		$$field = strip_tags(trim($value));
	}
	if(@is_array($errors))
	{
		$message_2 = "";
		foreach($errors as $value)
		{
			$message_2 .= $value." Please try again";
		}
		include("login_form.php");
		exit();
	} //end if errors are found

	/* check to see if username already exists */
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database) or die("Couldn't connect to server");
	$username = $cxn->real_escape_string($username);
	$sql = "SELECT username FROM Member WHERE username='$username'";
	$result = mysqli_query($cxn,$sql) or die("Query died: username.");
	$num = mysqli_num_rows($result);
	if($num > 0)
	{
		$message_2 = "$username already exists. Select another username.";
		include("login_form.php");
		exit();
	} // end if username already exists
	else // add new member to database
	{
		$sql = "INSERT INTO Member (username,createDate,password,firstName,email) VALUES ('$username',NOW(),md5('$password'),'$firstName','$email')";
		mysqli_query($cxn,$sql);
		$_SESSION['auth']="yes";
		$_SESSION['username'] = $username;
		header("Location: testing.php");
	}
	break;

	default:
	include("login_form.php");
}
?>

[Tenaciousmug]

So is there anyway to fix this?... I tried everything and it just doesn't want to work. I don't get it.

[JRM]

read this

[Tenaciousmug]

Oh ok. A friend just told me it's my login in general.
I was able to login as Username: Dyl
Password: abc123

I can use any password and it will let me login under any username in the database.

This is my login.php:

<?php
session_start();
switch (@$_POST['Button'])
{
	case "Log in";
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database);
	$fusername = $cxn->real_escape_string($_POST['fusername']);
	$sql = "SELECT `username` FROM `Member` WHERE `username`='$fusername'";
	$result = mysqli_query($cxn,$sql) or die("Query died: fusername");
	$num = mysqli_num_rows($result);
	if($num > 0)
	//username was found
	{
		$fpassword = $cxn->real_escape_string($_POST['fpassword']);
		$sql2 = "SELECT `username` FROM `Member` WHERE `username`='$fusername' AND `password`=md5('$fpassword')";
		$result2 = mysqli_query($cxn,$sql2) or die("Query died: fpassword");
		$num2 = mysqli_num_rows($result2);
		if($num > 0) //password matches
		{
			$_SESSION['auth']="yes";
			$_SESSION['username'] = $fusername;
			$sql = "INSERT INTO Login (username,loginTime) VALUES ('$fusername',NOW())";
			$result = mysqli_query($cxn,$sql) or die("Query died: insert");
			header("Location: testing.php");
		}
		else
		{
			$message_1="The username, '$fusername' exists. However you have not entered the correct password! Please try again.";
			$fusername=strip_tags(trim($fusername));
			include("login_form2.php");
		}
	}
	else // username was not found
	{
		$message_1 = "The username you entered does not exist! Please try again.";
		include("login_form2.php");
	}
	break;

	case "Register":
	/* Check for blanks */
	foreach($_POST as $field => $value)
	{
		if(empty($value))
		{
			$blanks[] = $field;
		}
		else
		{
			$good_data[$field] = strip_tags(trim($value));
		}
	}
	if(isset($blanks))
	{
		$message_2 = "The following fields are blank. Please enter the required information: ";
		foreach($blanks as $value)
		{
		$message_2 .="$value, ";
		}
		extract($good_data);
		include("login_form2.php");
		exit();
	}
	/* validate data */
	foreach($_POST as $field => $value)
	{
		if(!empty($value))
		{
			if(preg_match("/name/i",$field) and !preg_match("/user/i",$field) and !preg_match("/log/i",$field))
			{
				if(!preg_match("/^[A-Za-z' -]{1,15}$/",$value))
				{
					$errors[] = "$value is not a valid name. ";
				}
			}
			if(preg_match("/email/i",$field))
			{
				if(!preg_match("/^.+@.+\\..+$/",$value))
				{
					$errors[]="$value is not a valid email address.";
				}
			}
		} // end if not empty
	}
	foreach($_POST as $field => $value)
	{
		$$field = strip_tags(trim($value));
	}
	if(@is_array($errors))
	{
		$message_2 = "";
		foreach($errors as $value)
		{
			$message_2 .= $value." Please try again";
		}
		include("login_form2.php");
		exit();
	} //end if errors are found

	/* check to see if username already exists */
	include("haha.php");
	$cxn = mysqli_connect($host,$user,$password,$database) or die("Couldn't connect to server");
	$username = $cxn->real_escape_string($username);
	$sql = "SELECT `username` FROM `Member` WHERE `username`='$username'";
	$result = mysqli_query($cxn,$sql) or die("Query died: username.");
	$num = mysqli_num_rows($result);
	if($num > 0)
	{
		$message_2 = "$username already exists. Select another username.";
		include("login_form2.php");
		exit();
	} // end if username already exists
	else // add new member to database
	{
		$sql = "INSERT INTO Member (username,createDate,password,firstName,email) VALUES ('$username',NOW(),md5('$password'),'$firstName','$email')";
		mysqli_query($cxn,$sql);
		$_SESSION['auth']="yes";
		$_SESSION['username'] = $username;
		header("Location: testing.php");
	}
	break;

	default:
	include("login_form2.php");
}
?>

And this my login_form.php:

<?php
session_start();
$fusername = "fusername";
$fpassword = "fpassword";
$username = "username";
$password = "password";
$email = "email";
$firstName = "firstName";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Asterock Login</title>
</head>
<link rel = "stylesheet" type = "text/css" href = "indexcss.css" />
<link rel = "stylesheet" type = "text/css" href = "formcss.css" />

<body>

<div id="banner"></div>

<div id="navigation">
<img src="http://asterock.net/2010layouts/images/button.png" border="1"> - <img src="http://asterock.net/2010layouts/images/button.png" border="1"> - <img src="http://asterock.net/2010layouts/images/button.png" border="1"> - <img src="http://asterock.net/2010layouts/images/button.png" border="1"> - <img src="http://asterock.net/2010layouts/images/button.png" border="1">
</div>

<div id="content">
<font size=18>ASTEROCK</font>
<table border="0">
<tr>
<td width='15%' valign='top'>
<?php
echo "Username: {$_SESSION['username']}<br>\n";
?>
<?php
if(isset($_SESSION['username']))
{
include("haha.php");
$cxn = mysqli_connect($host,$user,$password,$database) or die(mysql_error());
$query = "SELECT `rp` FROM `Member` WHERE `username`='{$_SESSION['username']}'";
$result = mysqli_query($cxn,$query) or die(mysqli_error($cxn));
$row = mysqli_fetch_array($result) or die(mysqli_error());
echo "RP: {$row['rp']}";
}
else
{
echo "You must be logged in.";
}
?>
<div class="link"><a href="login.php">Login</a></div>
<div class="link"><a href="logout.php">Logout</a></div>
<div class="linkhead">General</div>
<div class="link"><a href="testing.php">Home</a></div>
<div class="link"><a href="explore.php">Explore</a></div>
<div class="link"><a href="games.php">Games</a></div>
<br><div class="linkhead">Communication</div>
<div class="link"><a href="forums.php">Forums</a></div>
<div class="link"><a href="forums.php">Forums</a></div>
<div class="link"><a href="forums.php">Forums</a></div>
<br><div class="linkhead">Blahdeedah</div>
<div class="link"><a href="forums.php">Forums</a></div>
<div class="link"><a href="forums.php">Forums</a></div>
</td>
<td width='85%' valign='top'>


<div id='wrapper'>
<div id='login'>
<form action="<?php echo $_SERVER['SCRIPT_NAME']?>" method="post">
<fieldset><legend>Login Form</legend>
<?php
if(isset($message_1))
{
echo "<p class='errors'>$message_1</p>\n";
}
$type2 = "password";
$type = "text";
echo "<div id='field'>
<b><label>Username</label></b>: <input id='style' type='$type' name='fusername' size='25' maxlength='15' align='left'></input>
<br><b><label>Password</label></b>: <input id='style' type='$type2' name='fpassword' size='25' maxlength='50' align='left'></input>";
?>
<br><input type='submit' name='Button' value='Log in' />
</fieldset>
</form>
<h3>If you already have an account, log in.</h3>
<h3>If you do not have an account, register now.</h3>
</div>

<div id='reg'>
<form action="<?php echo $_SERVER['SCRIPT_NAME']?>" method="post">
<fieldset><legend>Registration Form</legend>
By registering, you agree to abide to the <a href="TOC.php">Terms and Conditions</a>.
<?php
if(isset($message_2))
{
echo "<p class='errors'>$message_2</p>\n";
}
$type2 = "password";
$type = "text";
echo "<div id='field'>
<b><label>Username</label></b>: <input id='style' type='$type' name='username' size='40' maxlength='15' align='left'></input>
<br><b><label>Password</label></b>: <input id='style' type='$type2' name='password' size='40' maxlength='50' align='left'></input>
<br><b><label>First Name</label></b>: <input id='style' type='$type' name='firstName' size='40' maxlength='15' align='left'></input>
<br><b><label>Email</label></b>: <input id='style' type='$type' name='email' size='40' maxlength='50' align='left'></input>";
?>
<br><input type='submit' name='Button' value='Register' />
</fieldset>
</form>
</div>
</div>

</td>
</tr>
</table>

</div>

<div id="footer">
<p>&copy; 2010 Asterock. All Rights Reserved.
<br>About Us | Contact Us | Privacy Policy
<br>Use of this site signifies your acceptance of the Terms and Conditions.</p>
</div></body></html>

Anyone see any problem?.. Because I can't find one.

[Mogradjinn]

Line 19 in login.php:

if($num > 0) //password matches

Should be if($num2 > 0) //password matches

Otherwise you're effectively only testing username again, I think.