Don't understand mysql_real_escape_string
Posted: Mon Jan 17, 2011 7:01 am
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
And the pagination class file~
I have tried to use mysql_real_escape_string by placing this code up top the paginationclass file.
Then I went to index.php where under the
I edited it to
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).
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)";
?>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\">« Previous</a> ":"<a href=\"#\"><span class=\"inactive\">« 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 »</a>\n":"<href=\"#\"><span class=\"inactive\">» 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;
}
}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);
}Code: Select all
$query = "SELECT COUNT(*) FROM req1";Code: Select all
$query = mysql_safe( "SELECT COUNT(*) FROM req1 WHERE login = ? AND password = ?", array($login, $password) );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).