Page 1 of 1

Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 7:01 am
by SpiLoT
Ok, so I have an index page where it includes the login information needed to login into the database, and another class file where it gets the user's input of how many items per page he/she wants to view.
It's basically a pagination page.

Here are the codes~
Index.php

Code: Select all

<?php
			require('include/paginator.class.php');
			
			$host = 'X';
			$user = 'X';
			$pwd = 'X';
			$db = 'X';
			$connection = mysql_connect($host,$user,$pwd) or die("Could not connect");
			mysql_select_db($db) or die("Could not select database");
			
			$query =  "SELECT COUNT(*) FROM req1";
			
			$result = mysql_query($query) or die(mysql_error());
			$num_rows = mysql_fetch_row($result);
			
			$pages = new Paginator;
			$pages->items_total = $num_rows[0];
			$pages->mid_range = 9;
			$pages->paginate();
			
			echo $pages->display_pages();
			echo "<span class=\"\">".$pages->display_jump_menu().$pages->display_items_per_page()."</span>";
			
			$query = "SELECT sys_id,code,reqby,reqdate,linktodata FROM req1 ORDER BY sys_id DESC $pages->limit";
			
			$result = mysql_query($query) or die(mysql_error());
			
			echo "<table>";
			echo "<tr><th>System ID</th><th>Code</th><th>Requested By</th><th>Requested Date</th><th>Link-To-Data</th></tr>";
			while($row = mysql_fetch_row($result))
			{
				echo "<tr onmouseover=\"hilite(this)\" onmouseout=\"lowlite(this)\"><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td></tr>\n";
			}
			echo "</table>";
			
			echo $pages->display_pages();
			echo "<p class=\"paginate\">Page: $pages->current_page of $pages->num_pages</p>\n";
			echo "<p id=\"statment\">SELECT * FROM req1 $pages->limit (retrieve records $pages->low-$pages->high from table - $pages->items_total item total / $pages->items_per_page items per page)";
		?>
And the pagination class file~

Code: Select all

<?php

	class Paginator{
	var $items_per_page;
	var $items_total;
	var $current_page;
	var $num_pages;
	var $mid_range;
	var $low;
	var $high;
	var $limit;
	var $return;
	var $default_ipp = 25;
	var $querystring; 

	function Paginator()
	{
		$this->current_page = 1;
		$this->mid_range = 7;
		$this->items_per_page = (!empty($_GET['ipp'])) ? $_GET['ipp']:$this->default_ipp;
	}

	function paginate()
	{
		if($_GET['ipp'] == 'All')
		{
			$this->num_pages = ceil($this->items_total/$this->default_ipp);
			$this->items_per_page = $this->default_ipp;
		}
		else
		{
			if(!is_numeric($this->items_per_page) OR $this->items_per_page <= 0) $this->items_per_page = $this->default_ipp;
			$this->num_pages = ceil($this->items_total/$this->items_per_page);
		}
		$this->current_page = (int) $_GET['page']; // must be numeric > 0
		if($this->current_page < 1 Or !is_numeric($this->current_page)) $this->current_page = 1;
		if($this->current_page > $this->num_pages) $this->current_page = $this->num_pages;
		$prev_page = $this->current_page-1;
		$next_page = $this->current_page+1;

		if($_GET)
		{
			$args = explode("&",$_SERVER['QUERY_STRING']);
			foreach($args as $arg)
			{
				$keyval = explode("=",$arg);
				if($keyval[0] != "page" And $keyval[0] != "ipp") $this->querystring .= "&" . $arg;
			}
		}

		if($_POST)
		{
			foreach($_POST as $key=>$val)
			{
				if($key != "page" And $key != "ipp") $this->querystring .= "&$key=$val";
			}
		}

		if($this->num_pages > 10)
		{
			$this->return = ($this->current_page != 1 And $this->items_total >= 10) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$prev_page&ipp=$this->items_per_page$this->querystring\">&laquo; Previous</a> ":"<a href=\"#\"><span class=\"inactive\">&laquo; Previous</span></a> ";

			$this->start_range = $this->current_page - floor($this->mid_range/2);
			$this->end_range = $this->current_page + floor($this->mid_range/2);

			if($this->start_range <= 0)
			{
				$this->end_range += abs($this->start_range)+1;
				$this->start_range = 1;
			}
			if($this->end_range > $this->num_pages)
			{
				$this->start_range -= $this->end_range-$this->num_pages;
				$this->end_range = $this->num_pages;
			}
			$this->range = range($this->start_range,$this->end_range);

			for($i=1;$i<=$this->num_pages;$i++)
			{
				if($this->range[0] > 2 And $i == $this->range[0]) $this->return .= " ... ";
				// loop through all pages. if first, last, or in range, display
				if($i==1 Or $i==$this->num_pages Or in_array($i,$this->range))
				{
					$this->return .= ($i == $this->current_page And $_GET['page'] != 'All') ? "<a title=\"Go to page $i of $this->num_pages\" class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" title=\"Go to page $i of $this->num_pages\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\">$i</a> ";
				}
				if($this->range[$this->mid_range-1] < $this->num_pages-1 And $i == $this->range[$this->mid_range-1]) $this->return .= " ... ";
			}
			$this->return .= (($this->current_page != $this->num_pages And $this->items_total >= 10) And ($_GET['page'] != 'All')) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$next_page&ipp=$this->items_per_page$this->querystring\">Next &raquo;</a>\n":"<href=\"#\"><span class=\"inactive\">&raquo; Next</span></a>\n";
			$this->return .= ($_GET['page'] == 'All') ? "<a class=\"current\" style=\"margin-left:10px\" href=\"#\">All</a> \n":"<a class=\"paginate\" style=\"margin-left:10px\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\">All</a> \n";
		}
		else
		{
			for($i=1;$i<=$this->num_pages;$i++)
			{
				$this->return .= ($i == $this->current_page) ? "<a class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\">$i</a> ";
			}
			$this->return .= "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\">All</a> \n";
		}
		$this->low = ($this->current_page-1) * $this->items_per_page;
		$this->high = ($_GET['ipp'] == 'All') ? $this->items_total:($this->current_page * $this->items_per_page)-1;
		$this->limit = ($_GET['ipp'] == 'All') ? "":" LIMIT $this->low,$this->items_per_page";
	}

	function display_items_per_page()
	{
		$items = '';
		$ipp_array = array(10,25,50,100,'All');
		foreach($ipp_array as $ipp_opt)	$items .= ($ipp_opt == $this->items_per_page) ? "<option selected value=\"$ipp_opt\">$ipp_opt</option>\n":"<option value=\"$ipp_opt\">$ipp_opt</option>\n";
		return "<span class=\"paginate\">Items per page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page=1&ipp='+this[this.selectedIndex].value+'$this->querystring';return false\">$items</select>\n";
	}

	function display_jump_menu()
	{
		for($i=1;$i<=$this->num_pages;$i++)
		{
			$option .= ($i==$this->current_page) ? "<option value=\"$i\" selected>$i</option>\n":"<option value=\"$i\">$i</option>\n";
		}
		return "<span class=\"paginate\">Page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page='+this[this.selectedIndex].value+'&ipp=$this->items_per_page$this->querystring';return false\">$option</select>\n";
	}

	function display_pages()
	{
		return $this->return;
	}
}
I have tried to use mysql_real_escape_string by placing this code up top the paginationclass file.

Code: Select all

function mysql_safe($query,$params=false) { 
		if ($params) { 
			foreach ($params as &$v) { $v = mysql_real_escape_string($v); }    # Escaping parameters 
			# str_replace - replacing ? -> %s. %s is ugly in raw sql query 
			# vsprintf - replacing all %s to parameters 
			$query = vsprintf( str_replace("?","'%s'",$query), $params );    
			$query = mysql_query($query);    # Perfoming escaped query 
		} else { 
			$query = mysql_query($query);    # If no params... 
		} 
	
		return ($sql_query); 
	}
Then I went to index.php where under the

Code: Select all

$query =  "SELECT COUNT(*) FROM req1";
I edited it to

Code: Select all

$query = mysql_safe( "SELECT COUNT(*) FROM req1 WHERE login = ? AND password = ?", array($login, $password) );
And when I launch the page, it gives me "Query was empty".

I have read about 2 hours on it, but I really haven't figured it out, it gets all clumsy and then I restart by removing all the code and re doing the whole thing.
I have to get the code up running without someone messing out with the tables cause somehow whenever the page is up for more than 1-2 days, the whole database (originaly about 4000 entries) gets emptied or reduced to very very low entries (10~20).
And beside the fact that my dad's customer wants it be always on. I can't just take it slow and finish it. It's hard.

I'll be more than happy if someone linked me to a tutorial, book, or anything that will surely learn me how to do it with any sql statement.
But for now, how could it be done to make it a bit secure?

P.S. Here's a snapshot of the index page
Link : http://img441.imageshack.us/img441/818/98658525.png (100kb).

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 7:49 am
by Darhazer

Code: Select all

 return ($sql_query); 
$sql_query is not defined variable.

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 8:19 am
by SpiLoT
Darhazer wrote:

Code: Select all

 return ($sql_query); 
$sql_query is not defined variable.
Hmm, lets say I define it. Is this a correct way of using mysql_real_escape_string?

Thank you so much for your time.

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 8:30 am
by Darhazer
yes, it's ok, at least for me this produces a correct query

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 10:17 am
by matthijs
I've never seen the function being used like that. You should just use it like

Code: Select all

$escapedlogin = mysql_real_escape_string($login);
$escapedpass = mysql_real_escape_string($pass);
"SELECT COUNT(*) FROM req1 WHERE login = $escapedlogin AND password = $escapedpass"
See also
http://phpsecurity.org/code/ch01-4

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 11:39 am
by SpiLoT
matthijs wrote:I've never seen the function being used like that. You should just use it like

Code: Select all

$escapedlogin = mysql_real_escape_string($login);
$escapedpass = mysql_real_escape_string($pass);
"SELECT COUNT(*) FROM req1 WHERE login = $escapedlogin AND password = $escapedpass"
See also
http://phpsecurity.org/code/ch01-4
The code outputs wrong syntax.
That's how I made it~

Code: Select all

			
			$escapedlogin = mysql_real_escape_string($login);
			$escapedpass = mysql_real_escape_string($pass);
			$query = "SELECT COUNT(*) FROM req1 WHERE login = $escapedlogin AND password = $escapedpass ";
And the error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND password =' at line 1

I tried to put $escapedlogin, $scapedpass between ' ', didn't help... placing them without spaces between login/password, =, and $scapedlogin/$scapedpass. haven't worked either.

I just bought that book by Chris Shiflett btw. I'm reading through it now...

Anyways, if you could only just help me get index.php work out, I'll be more than grateful.

Thanks guys.

Re: Don't understand mysql_real_escape_string

Posted: Mon Jan 17, 2011 4:08 pm
by matthijs
If you copy the code from the example on shifletts' page it should definitely be the right syntax. Else check all the other lines of your code. Maybe you forgot a quote/character somewhere else

Re: Don't understand mysql_real_escape_string

Posted: Tue Jan 18, 2011 2:13 am
by Darhazer
You are missing the quotes around values:

Code: Select all

$query = "SELECT COUNT(*) FROM req1 WHERE `login` = '$escapedlogin' AND `password` = '$escapedpass' ";
But actually using placeholders (?) and array of values is a common practice and there is nothing wrong with your mysql_safe function

Re: Don't understand mysql_real_escape_string

Posted: Tue Jan 18, 2011 2:50 am
by SpiLoT
Darhazer wrote:You are missing the quotes around values:

Code: Select all

$query = "SELECT COUNT(*) FROM req1 WHERE `login` = '$escapedlogin' AND `password` = '$escapedpass' ";
But actually using placeholders (?) and array of values is a common practice and there is nothing wrong with your mysql_safe function

So the "return ($sql_query);" where the $sql_query is not defined, so I changed it to $query.
"return ($query);"? Correct? It gives "Query was empty". :(

But from the sql statement it checks the column Login & Password, correct? I don't have those in the database... Anyone can view up the table.
Am I supposed to have a login page and create sessions for users?

That's where I get confused using mysql_real_escape_string.

And what about pagination.class.php? Does it need any kind of using escape?
Like for example, it gets the user's IPP value

:/