mysql_real_escape_string()

gfoisy · December 16, 2008

I am a novice to PHP and MySQL, so please excuse my lack of knowledge. I have a MySQL database with just a few text and VARChar fields, and I use a web-based PHP form to add, edit and delete information into this MySQL database. It works pretty good and is brand new so I'm still discovering the kinks. I just figured out that when I enter information that contains an apostrophe in it, for example "O'Briens Pub", I received syntax errors when posting the form.

After doing some investigating, I found that apostrophes are a problem for MySQL, and that there's a function - mysql_real_escape_string() that will eliminate the problem and allow me to enter apostrophes in any text string.

As an example I found that the code is:

<?php 
$user_name = mysql_real_escape_string ($_POST['user_name']); 
?>

I assume that user_name is the name of the field in the MySQL database, so here's my novice question:

Where exactly do I put this code in my PHP page?

And, if I want to apply it to all fields, do I enter this code for every field?

Here's the area I think might be invloved in my PHP page, but I'm not sure:

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `gigs` (`Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost`) values (" .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Time"], true).", " .sqlvalue(@$_POST["City/ST"], true).", " .sqlvalue(@$_POST["Venue"], true).", " .sqlvalue(@$_POST["Description"], true).", " .sqlvalue(@$_POST["Cost"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `gigs` set `Date`=" .sqlvalue(@$_POST["Date"], true).", `Time`=" .sqlvalue(@$_POST["Time"], true).", `City/ST`=" .sqlvalue(@$_POST["City/ST"], true).", `Venue`=" .sqlvalue(@$_POST["Venue"], true).", `Description`=" .sqlvalue(@$_POST["Description"], true).", `Cost`=" .sqlvalue(@$_POST["Cost"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());

HELP!

THANKS!

rhodesa · December 16, 2008

well, the sqlvalue() function looks like it should be taking care of the apostrophes. what is the code for that function?

gfoisy · December 16, 2008

This it?

function sqlvalue($val, $quote)

{

if ($quote)

$tmp = sqlstr($val);

else

$tmp = $val;

if ($tmp == "")

$tmp = "NULL";

elseif ($quote)

$tmp = "'".$tmp."'";

return $tmp;

}

rhodesa · December 16, 2008

haha..ok...following the functions...what's the code for sqlstr() ?

gfoisy · December 16, 2008

Sorry.... did I mention I'm an idiot? When I said novice, I meant it. haha

function sqlvalue($val, $quote)
{
  if ($quote)
    $tmp = sqlstr($val);
  else
    $tmp = $val;
  if ($tmp == "")
    $tmp = "NULL";
  elseif ($quote)
    $tmp = "'".$tmp."'";
  return $tmp;
}

function sqlstr($val)
{
  return str_replace("'", "''", $val);
}

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT `Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost` FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `gigs` (`Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost`) values (" .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Time"], true).", " .sqlvalue(@$_POST["City/ST"], true).", " .sqlvalue(@$_POST["Venue"], true).", " .sqlvalue(@$_POST["Description"], true).", " .sqlvalue(@$_POST["Cost"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `gigs` set `Date`=" .sqlvalue(@$_POST["Date"], true).", `Time`=" .sqlvalue(@$_POST["Time"], true).", `City/ST`=" .sqlvalue(@$_POST["City/ST"], true).", `Venue`=" .sqlvalue(@$_POST["Venue"], true).", `Description`=" .sqlvalue(@$_POST["Description"], true).", `Cost`=" .sqlvalue(@$_POST["Cost"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
  


}

function sql_delete()
{
  global $conn;

  $sql = "delete from `gigs` where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(`Date`";
  if (@$_POST["xDate"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xDate"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Time`";
  if (@$_POST["xTime"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xTime"], true);
  };
  $pk .= ") and ";
  $pk .= "(`City/ST`";
  if (@$_POST["xCity/ST"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCity/ST"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Cost`";
  if (@$_POST["xCost"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCost"], true);
  };
  $pk .= ")";
  return $pk;
}
?>

rhodesa · December 16, 2008

it's using MSSQL's method of escaping quotes. try replacing this function:

function sqlstr($val)
{
  return mysql_real_escape_string($val);
}

where did you get this code from?

gfoisy · December 16, 2008

It's from a PHP Code Generator program called "PHP Generator for MySQL".

rhodesa · December 16, 2008

interesting...again, that escape method is for MSSQL, not MySQL.

did the updated function fix your problem?

gfoisy · December 16, 2008

Am I inserting this instead but with my real field name?

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

And where?

SORRY.....

rhodesa · December 16, 2008

just change the sqlstr() function to this:

function sqlstr($val)
{
  return mysql_real_escape_string($val);
}

and leave everything else the same. the following functions:

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `gigs` (`Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost`) values (" .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Time"], true).", " .sqlvalue(@$_POST["City/ST"], true).", " .sqlvalue(@$_POST["Venue"], true).", " .sqlvalue(@$_POST["Description"], true).", " .sqlvalue(@$_POST["Cost"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `gigs` set `Date`=" .sqlvalue(@$_POST["Date"], true).", `Time`=" .sqlvalue(@$_POST["Time"], true).", `City/ST`=" .sqlvalue(@$_POST["City/ST"], true).", `Venue`=" .sqlvalue(@$_POST["Venue"], true).", `Description`=" .sqlvalue(@$_POST["Description"], true).", `Cost`=" .sqlvalue(@$_POST["Cost"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());

}

use sqlvalue() which use sqlstr(), which use mysql_real_escape_string()

gfoisy · December 16, 2008

Ok.... it posts with no errors, however, it returns a "\" before the apostrophe, like this:

O\'Brien

??

rhodesa · December 16, 2008

are you still using:

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

if so, remove it

edit: if not, post how you are calling the functions

gfoisy · December 16, 2008

function sqlvalue($val, $quote)

{

if ($quote)

$tmp = sqlstr($val);

else

$tmp = $val;

if ($tmp == "")

$tmp = "NULL";

elseif ($quote)

$tmp = "'".$tmp."'";

return $tmp;

}

function sqlstr($val)

{

return mysql_real_escape_string($val);

}

gfoisy · December 16, 2008

Not using:

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

rhodesa · December 16, 2008

yes, but how are you CALLING the functions? what is your code that USES the functions?

gfoisy · December 16, 2008

I guess I don't know.....

Here's the code from that point down:

function sqlvalue($val, $quote)
{
  if ($quote)
    $tmp = sqlstr($val);
  else
    $tmp = $val;
  if ($tmp == "")
    $tmp = "NULL";
  elseif ($quote)
    $tmp = "'".$tmp."'";
  return $tmp;
}

function sqlstr($val)
{
return mysql_real_escape_string($val);
}

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT `Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost` FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `gigs` (`Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost`) values (" .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Time"], true).", " .sqlvalue(@$_POST["City/ST"], true).", " .sqlvalue(@$_POST["Venue"], true).", " .sqlvalue(@$_POST["Description"], true).", " .sqlvalue(@$_POST["Cost"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `gigs` set `Date`=" .sqlvalue(@$_POST["Date"], true).", `Time`=" .sqlvalue(@$_POST["Time"], true).", `City/ST`=" .sqlvalue(@$_POST["City/ST"], true).", `Venue`=" .sqlvalue(@$_POST["Venue"], true).", `Description`=" .sqlvalue(@$_POST["Description"], true).", `Cost`=" .sqlvalue(@$_POST["Cost"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
  


}

function sql_delete()
{
  global $conn;

  $sql = "delete from `gigs` where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(`Date`";
  if (@$_POST["xDate"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xDate"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Time`";
  if (@$_POST["xTime"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xTime"], true);
  };
  $pk .= ") and ";
  $pk .= "(`City/ST`";
  if (@$_POST["xCity/ST"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCity/ST"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Cost`";
  if (@$_POST["xCost"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCost"], true);
  };
  $pk .= ")";
  return $pk;
}
?>

rhodesa · December 16, 2008

these are only function declarations. running this script won't do anything. there has to be code somewhere that executes these functions

gfoisy · December 16, 2008

OR, is it more this area?

<?php function addrec()
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php">Index Page</a></td>
</tr>
</table>
<hr size="1" noshade>
<form enctype="multipart/form-data" action="index.php" method="post">
<p><input type="hidden" name="sql" value="insert"></p>
<?php
$row = array(
  "Date" => "",
  "Time" => "",
  "City/ST" => "",
  "Venue" => "",
  "Description" => "",
  "Cost" => "");
showroweditor($row, false);
?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php } ?>

<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<br>
<hr size="1" noshade>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php?a=add">Add Record</a></td>
<td><a href="index.php?a=edit&recid=<?php echo $recid ?>">Edit Record</a></td>
<td><a href="index.php?a=del&recid=<?php echo $recid ?>">Delete Record</a></td>
</tr>
</table>
<?php
  mysql_free_result($res);
} ?>

<?php function editrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("edit", $recid, $count);
?>
<br>
<form enctype="multipart/form-data" action="index.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xDate" value="<?php echo $row["Date"] ?>">
<input type="hidden" name="xTime" value="<?php echo $row["Time"] ?>">
<input type="hidden" name="xCity/ST" value="<?php echo $row["City/ST"] ?>">
<input type="hidden" name="xCost" value="<?php echo $row["Cost"] ?>">
<?php showroweditor($row, true); ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

<?php function deleterec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("del", $recid, $count);
?>
<br>
<form action="index.php" method="post">
<input type="hidden" name="sql" value="delete">
<input type="hidden" name="xDate" value="<?php echo $row["Date"] ?>">
<input type="hidden" name="xTime" value="<?php echo $row["Time"] ?>">
<input type="hidden" name="xCity/ST" value="<?php echo $row["City/ST"] ?>">
<input type="hidden" name="xCost" value="<?php echo $row["Cost"] ?>">
<?php showrow($row, $recid) ?>
<p><input type="submit" name="action" value="Confirm"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

gfoisy · December 16, 2008

Here's the entire page code:

THANKS FOR YOUR HELP!

<?php session_start();
  if (isset($_GET["order"])) $order = @$_GET["order"];
  if (isset($_GET["type"])) $ordtype = @$_GET["type"];

  if (isset($_POST["filter"])) $filter = @$_POST["filter"];
  if (isset($_POST["filter_field"])) $filterfield = @$_POST["filter_field"];
  $wholeonly = false;
  if (isset($_POST["wholeonly"])) $wholeonly = @$_POST["wholeonly"];

  if (!isset($order) && isset($_SESSION["order"])) $order = $_SESSION["order"];
  if (!isset($ordtype) && isset($_SESSION["type"])) $ordtype = $_SESSION["type"];
  if (!isset($filter) && isset($_SESSION["filter"])) $filter = $_SESSION["filter"];
  if (!isset($filterfield) && isset($_SESSION["filter_field"])) $filterfield = $_SESSION["filter_field"];

?>


<html>
<head>
<title>Garry's Gigs</title>
<meta name="generator" http-equiv="content-type" content="text/html">
<style type="text/css">
  body {
    background-color: #FFFFFF;
    color: #004080;
    font-family: Arial;
    font-size: 12px;
  }
  .bd {
    background-color: #FFFFFF;
    color: #004080;
    font-family: Arial;
    font-size: 12px;
  }
  .tbl {
    background-color: #FFFFFF;
  }
  a:link { 
    color: #FF0000;
    font-family: Arial;
    font-size: 12px;
  }
  a:active { 
    color: #0000FF;
    font-family: Arial;
    font-size: 12px;
  }
  a:visited { 
    color: #800080;
    font-family: Arial;
    font-size: 12px;
  }
  .hr {
    background-color: #336699;
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:link {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:active {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  a.hr:visited {
    color: #FFFFFF;
    font-family: Arial;
    font-size: 12px;
  }
  .dr {
    background-color: #FFFFFF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
  .sr {
    background-color: #FFFFCF;
    color: #000000;
    font-family: Arial;
    font-size: 12px;
  }
</style>
</head>
<body>
<table class="bd" width="100%"><tr><td class="hr"><h2>Garry's Gigs</h2></td></tr></table>
<?php
  if (!login()) exit;
?>
<div style="float: right"><a href="index.php?a=logout">[ Logout ]</a></div>
<br>
<?php
  $conn = connect();
  $showrecs = 20;
  $pagerange = 10;

  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;

  $sql = @$_POST["sql"];

  switch ($sql) {
    case "insert":
      sql_insert();
      break;
    case "update":
      sql_update();
      break;
    case "delete":
      sql_delete();
      break;
  }

  switch ($a) {
    case "add":
      addrec();
      break;
    case "view":
      viewrec($recid);
      break;
    case "edit":
      editrec($recid);
      break;
    case "del":
      deleterec($recid);
      break;
    default:
      select();
      break;
  }

  if (isset($order)) $_SESSION["order"] = $order;
  if (isset($ordtype)) $_SESSION["type"] = $ordtype;
  if (isset($filter)) $_SESSION["filter"] = $filter;
  if (isset($filterfield)) $_SESSION["filter_field"] = $filterfield;
  if (isset($wholeonly)) $_SESSION["wholeonly"] = $wholeonly;

  mysql_close($conn);
?>
<table class="bd" width="100%"><tr><td class="hr"> </td></tr></table>
</body>
</html>

<?php function select()
  {
  global $a;
  global $showrecs;
  global $page;
  global $filter;
  global $filterfield;
  global $wholeonly;
  global $order;
  global $ordtype;


  if ($a == "reset") {
    $filter = "";
    $filterfield = "";
    $wholeonly = "";
    $order = "";
    $ordtype = "";
  }

  $checkstr = "";
  if ($wholeonly) $checkstr = " checked";
  if ($ordtype == "asc") { $ordtypestr = "desc"; } else { $ordtypestr = "asc"; }
  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr><td>Table: gigs</td></tr>
<tr><td>Records shown <?php echo $startrec + 1 ?> - <?php echo $reccount ?> of <?php echo $count ?></td></tr>
</table>
<hr size="1" noshade>
<form action="index.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><b>Custom Filter</b> </td>
<td><input type="text" name="filter" value="<?php echo $filter ?>"></td>
<td><select name="filter_field">
<option value="">All Fields</option>
<option value="<?php echo "Date" ?>"<?php if ($filterfield == "Date") { echo "selected"; } ?>><?php echo htmlspecialchars("Date") ?></option>
<option value="<?php echo "Time" ?>"<?php if ($filterfield == "Time") { echo "selected"; } ?>><?php echo htmlspecialchars("Time") ?></option>
<option value="<?php echo "City/ST" ?>"<?php if ($filterfield == "City/ST") { echo "selected"; } ?>><?php echo htmlspecialchars("City/ST") ?></option>
<option value="<?php echo "Venue" ?>"<?php if ($filterfield == "Venue") { echo "selected"; } ?>><?php echo htmlspecialchars("Venue") ?></option>
<option value="<?php echo "Description" ?>"<?php if ($filterfield == "Description") { echo "selected"; } ?>><?php echo htmlspecialchars("Description") ?></option>
<option value="<?php echo "Cost" ?>"<?php if ($filterfield == "Cost") { echo "selected"; } ?>><?php echo htmlspecialchars("Cost") ?></option>
</select></td>
<td><input type="checkbox" name="wholeonly"<?php echo $checkstr ?>>Whole words only</td>
</td></tr>
<tr>
<td> </td>
<td><input type="submit" name="action" value="Apply Filter"></td>
<td><a href="index.php?a=reset">Reset Filter</a></td>
</tr>
</table>
</form>
<hr size="1" noshade>
<?php showpagenav($page, $pagecount); ?>
<br>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="100%">
<tr>
<td class="hr"> </td>
<td class="hr"> </td>
<td class="hr"> </td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Date" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("Date") ?></b></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Time" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("Time") ?></b></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "City/ST" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("City/ST") ?></b></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Venue" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("Venue") ?></b></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Description" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("Description") ?></b></a></td>
<td class="hr"><a class="hr" href="index.php?order=<?php echo "Cost" ?>&type=<?php echo $ordtypestr ?>">
<b><?php echo htmlspecialchars("Cost") ?></b></a></td>
</tr>
<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
    $style = "dr";
    if ($i % 2 != 0) {
      $style = "sr";
    }
?>
<tr>
<td class="<?php echo $style ?>"><a href="index.php?a=view&recid=<?php echo $i ?>">View</a></td>
<td class="<?php echo $style ?>"><a href="index.php?a=edit&recid=<?php echo $i ?>">Edit</a></td>
<td class="<?php echo $style ?>"><a href="index.php?a=del&recid=<?php echo $i ?>">Delete</a></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Date"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Time"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["City/ST"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Venue"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Description"]) ?></td>
<td class="<?php echo $style ?>"><?php echo htmlspecialchars($row["Cost"]) ?></td>
</tr>
<?php
  }
  mysql_free_result($res);
?>
</table>
<br>
<?php showpagenav($page, $pagecount); ?>
<?php } ?>

<?php function login()
{
  global $_POST;
  global $_SESSION;

  global $_GET;
  if (isset($_GET["a"]) && ($_GET["a"] == 'logout')) $_SESSION["logged_in"] = false;
  if (!isset($_SESSION["logged_in"])) $_SESSION["logged_in"] = false;
  if (!$_SESSION["logged_in"]) {
    $login = "";
    $password = "";
    if (isset($_POST["login"])) $login = @$_POST["login"];
    if (isset($_POST["password"])) $password = @$_POST["password"];

    if (($login != "") && ($password != "")) {
      if (($login == "test") && ($password == "test1234")) {
        $_SESSION["logged_in"] = true;
    }
    else {
?>
<p><b><font color="-1">Sorry, the login/password combination you've entered is invalid</font></b></p>
<?php } } }if (isset($_SESSION["logged_in"]) && (!$_SESSION["logged_in"])) { ?>
<form action="index.php" method="post">
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td>Login</td>
<td><input type="text" name="login" value="<?php echo $login ?>"></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" name="password" value="<?php echo $password ?>"></td>
</tr>
<tr>
<td><input type="submit" name="action" value="Login"></td>
</tr>
</table>
</form>
<?php
  }
  if (!isset($_SESSION["logged_in"])) $_SESSION["logged_in"] = false;
  return $_SESSION["logged_in"];
} ?>

<?php function showrow($row, $recid)
  {
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("Date")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Date"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Time")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Time"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("City/ST")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["City/ST"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Venue")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Venue"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Description")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Description"]) ?></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Cost")." " ?></td>
<td class="dr"><?php echo htmlspecialchars($row["Cost"]) ?></td>
</tr>
</table>
<?php } ?>

<?php function showroweditor($row, $iseditmode)
  {
  global $conn;
?>
<table class="tbl" border="0" cellspacing="1" cellpadding="5"width="50%">
<tr>
<td class="hr"><?php echo htmlspecialchars("Date")." " ?></td>
<td class="dr"><input type="text" name="Date" value="<?php echo str_replace('"', '"', trim($row["Date"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Time")." " ?></td>
<td class="dr"><input type="text" name="Time" maxlength="10" value="<?php echo str_replace('"', '"', trim($row["Time"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("City/ST")." " ?></td>
<td class="dr"><input type="text" name="City/ST" maxlength="45" value="<?php echo str_replace('"', '"', trim($row["City/ST"])) ?>"></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Venue")." " ?></td>
<td class="dr"><font face="MS Sans Serif">
<textarea cols="35" rows="4" name="Venue" maxlength="100" style="font-family: MS Reference Sans Serif"><?php echo str_replace('"', '"', trim($row["Venue"])) ?></textarea></font></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Description")." " ?></td>
<td class="dr">
<textarea cols="35" rows="4" name="Description" maxlength="100" style="font-family: MS Reference Sans Serif"><?php echo str_replace('"', '"', trim($row["Description"])) ?></textarea></td>
</tr>
<tr>
<td class="hr"><?php echo htmlspecialchars("Cost")." " ?></td>
<td class="dr"><input type="text" name="Cost" maxlength="10" value="<?php echo str_replace('"', '"', trim($row["Cost"])) ?>"></td>
</tr>
</table>
<?php } ?>

<?php function showpagenav($page, $pagecount)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php?a=add">Add Record</a> </td>
<?php if ($page > 1) { ?>
<td><a href="index.php?page=<?php echo $page - 1 ?>"><< Prev</a> </td>
<?php } ?>
<?php
  global $pagerange;

  if ($pagecount > 1) {

  if ($pagecount % $pagerange != 0) {
    $rangecount = intval($pagecount / $pagerange) + 1;
  }
  else {
    $rangecount = intval($pagecount / $pagerange);
  }
  for ($i = 1; $i < $rangecount + 1; $i++) {
    $startpage = (($i - 1) * $pagerange) + 1;
    $count = min($i * $pagerange, $pagecount);

    if ((($page >= $startpage) && ($page <= ($i * $pagerange)))) {
      for ($j = $startpage; $j < $count + 1; $j++) {
        if ($j == $page) {
?>
<td><b><?php echo $j ?></b></td>
<?php } else { ?>
<td><a href="index.php?page=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php } } } else { ?>
<td><a href="index.php?page=<?php echo $startpage ?>"><?php echo $startpage ."..." .$count ?></a></td>
<?php } } } ?>
<?php if ($page < $pagecount) { ?>
<td> <a href="index.php?page=<?php echo $page + 1 ?>">Next >></a> </td>
<?php } ?>
</tr>
</table>
<?php } ?>

<?php function showrecnav($a, $recid, $count)
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php">Index Page</a></td>
<?php if ($recid > 0) { ?>
<td><a href="index.php?a=<?php echo $a ?>&recid=<?php echo $recid - 1 ?>">Prior Record</a></td>
<?php } if ($recid < $count - 1) { ?>
<td><a href="index.php?a=<?php echo $a ?>&recid=<?php echo $recid + 1 ?>">Next Record</a></td>
<?php } ?>
</tr>
</table>
<hr size="1" noshade>
<?php } ?>

<?php function addrec()
{
?>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php">Index Page</a></td>
</tr>
</table>
<hr size="1" noshade>
<form enctype="multipart/form-data" action="index.php" method="post">
<p><input type="hidden" name="sql" value="insert"></p>
<?php
$row = array(
  "Date" => "",
  "Time" => "",
  "City/ST" => "",
  "Venue" => "",
  "Description" => "",
  "Cost" => "");
showroweditor($row, false);
?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php } ?>

<?php function viewrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("view", $recid, $count);
?>
<br>
<?php showrow($row, $recid) ?>
<br>
<hr size="1" noshade>
<table class="bd" border="0" cellspacing="1" cellpadding="4">
<tr>
<td><a href="index.php?a=add">Add Record</a></td>
<td><a href="index.php?a=edit&recid=<?php echo $recid ?>">Edit Record</a></td>
<td><a href="index.php?a=del&recid=<?php echo $recid ?>">Delete Record</a></td>
</tr>
</table>
<?php
  mysql_free_result($res);
} ?>

<?php function editrec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("edit", $recid, $count);
?>
<br>
<form enctype="multipart/form-data" action="index.php" method="post">
<input type="hidden" name="sql" value="update">
<input type="hidden" name="xDate" value="<?php echo $row["Date"] ?>">
<input type="hidden" name="xTime" value="<?php echo $row["Time"] ?>">
<input type="hidden" name="xCity/ST" value="<?php echo $row["City/ST"] ?>">
<input type="hidden" name="xCost" value="<?php echo $row["Cost"] ?>">
<?php showroweditor($row, true); ?>
<p><input type="submit" name="action" value="Post"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

<?php function deleterec($recid)
{
  $res = sql_select();
  $count = sql_getrecordcount();
  mysql_data_seek($res, $recid);
  $row = mysql_fetch_assoc($res);
  showrecnav("del", $recid, $count);
?>
<br>
<form action="index.php" method="post">
<input type="hidden" name="sql" value="delete">
<input type="hidden" name="xDate" value="<?php echo $row["Date"] ?>">
<input type="hidden" name="xTime" value="<?php echo $row["Time"] ?>">
<input type="hidden" name="xCity/ST" value="<?php echo $row["City/ST"] ?>">
<input type="hidden" name="xCost" value="<?php echo $row["Cost"] ?>">
<?php showrow($row, $recid) ?>
<p><input type="submit" name="action" value="Confirm"></p>
</form>
<?php
  mysql_free_result($res);
} ?>

<?php function connect()
{
  $conn = mysql_connect("localhost", "username", "password");
  mysql_select_db("database_name");
  return $conn;

}

function sqlvalue($val, $quote)
{
  if ($quote)
    $tmp = sqlstr($val);
  else
    $tmp = $val;
  if ($tmp == "")
    $tmp = "NULL";
  elseif ($quote)
    $tmp = "'".$tmp."'";
  return $tmp;
}

function sqlstr($val)
{
return mysql_real_escape_string($val);
}

function sql_select()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT `Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost` FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  if (isset($order) && $order!='') $sql .= " order by `" .sqlstr($order) ."`";
  if (isset($ordtype) && $ordtype!='') $sql .= " " .sqlstr($ordtype);
  $res = mysql_query($sql, $conn) or die(mysql_error());
  return $res;
}

function sql_getrecordcount()
{
  global $conn;
  global $order;
  global $ordtype;
  global $filter;
  global $filterfield;
  global $wholeonly;

  $filterstr = sqlstr($filter);
  if (!$wholeonly && isset($wholeonly) && $filterstr!='') $filterstr = "%" .$filterstr ."%";
  $sql = "SELECT COUNT(*) FROM `gigs`";
  if (isset($filterstr) && $filterstr!='' && isset($filterfield) && $filterfield!='') {
    $sql .= " where " .sqlstr($filterfield) ." like '" .$filterstr ."'";
  } elseif (isset($filterstr) && $filterstr!='') {
    $sql .= " where (`Date` like '" .$filterstr ."') or (`Time` like '" .$filterstr ."') or (`City/ST` like '" .$filterstr ."') or (`Venue` like '" .$filterstr ."') or (`Description` like '" .$filterstr ."') or (`Cost` like '" .$filterstr ."')";
  }
  $res = mysql_query($sql, $conn) or die(mysql_error());
  $row = mysql_fetch_assoc($res);
  reset($row);
  return current($row);
}

function sql_insert()
{
  global $conn;
  global $_POST;

  $sql = "insert into `gigs` (`Date`, `Time`, `City/ST`, `Venue`, `Description`, `Cost`) values (" .sqlvalue(@$_POST["Date"], true).", " .sqlvalue(@$_POST["Time"], true).", " .sqlvalue(@$_POST["City/ST"], true).", " .sqlvalue(@$_POST["Venue"], true).", " .sqlvalue(@$_POST["Description"], true).", " .sqlvalue(@$_POST["Cost"], true).")";
  mysql_query($sql, $conn) or die(mysql_error());
}

function sql_update()
{
  global $conn;
  global $_POST;

  $sql = "update `gigs` set `Date`=" .sqlvalue(@$_POST["Date"], true).", `Time`=" .sqlvalue(@$_POST["Time"], true).", `City/ST`=" .sqlvalue(@$_POST["City/ST"], true).", `Venue`=" .sqlvalue(@$_POST["Venue"], true).", `Description`=" .sqlvalue(@$_POST["Description"], true).", `Cost`=" .sqlvalue(@$_POST["Cost"], true) ." where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
  


}

function sql_delete()
{
  global $conn;

  $sql = "delete from `gigs` where " .primarykeycondition();
  mysql_query($sql, $conn) or die(mysql_error());
}
function primarykeycondition()
{
  global $_POST;
  $pk = "";
  $pk .= "(`Date`";
  if (@$_POST["xDate"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xDate"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Time`";
  if (@$_POST["xTime"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xTime"], true);
  };
  $pk .= ") and ";
  $pk .= "(`City/ST`";
  if (@$_POST["xCity/ST"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCity/ST"], true);
  };
  $pk .= ") and ";
  $pk .= "(`Cost`";
  if (@$_POST["xCost"] == "") {
    $pk .= " IS NULL";
  }else{
  $pk .= " = " .sqlvalue(@$_POST["xCost"], true);
  };
  $pk .= ")";
  return $pk;
}
?>

rhodesa · December 16, 2008

first, when posting code, click the CODE button first...it's the one with the # sign in the toolbar

ok...getting closer i think. can you post the Full Syntax error you are getting including all the mysql info and line numbers?

also, do you have magic_quotes() enabled?

http://us2.php.net/magic_quotes

gfoisy · December 16, 2008

Just to clarify..... I'm not receiving any errors anymore.

It just inserts a \ infront of the apostrophe.

For example:

O\'Brien looks like this versus - O'Brien

gfoisy · December 16, 2008

Not aware of Magic Quotes on... although I don't know anything about it either, other than what I just read from your link you sent.

rhodesa · December 16, 2008

weird...in a file, put the following code and run it:

<?php
  if(get_magic_quotes_gpc())
    print "Magic Quotes Enabled";
  else
    print "Magic Quotes Disabled";
?>

i can't imagine magic quotes being on though...since if they are, you should never have had a problem to begin with...

gfoisy · December 16, 2008

I inserted that code at the top of my php page and published it and ran it.

Now at the top of my form page, it says: Magic Quotes Enabled".

The form comes up fine.... but when I edit a record and put the apostrophe back in and then post it, it returns the data with a \ before the apostrophe still.

Ex: O\'Brien

rhodesa · December 16, 2008

ok...you should disable magic quotes

http://us2.php.net/manual/en/security.magicquotes.disabling.php

Following Example #1 preferable or Example #2 if you can't do #1

Sign In

mysql_real_escape_string()

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information