gfoisy Posted December 16, 2008 Share Posted 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! Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted December 16, 2008 well, the sqlvalue() function looks like it should be taking care of the apostrophes. what is the code for that function? Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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; } Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted December 16, 2008 haha..ok...following the functions...what's the code for sqlstr() ? Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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; } ?> Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted December 16, 2008 It's from a PHP Code Generator program called "PHP Generator for MySQL". Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted December 16, 2008 interesting...again, that escape method is for MSSQL, not MySQL. did the updated function fix your problem? Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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..... Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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() Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted December 16, 2008 Ok.... it posts with no errors, however, it returns a "\" before the apostrophe, like this: O\'Brien ?? Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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); } Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted December 16, 2008 Not using: $user_name = mysql_real_escape_string ($_POST['user_name']); Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted December 16, 2008 yes, but how are you CALLING the functions? what is your code that USES the functions? Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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; } ?> Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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); } ?> Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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; } ?> Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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... Quote Link to comment Share on other sites More sharing options...
gfoisy Posted December 16, 2008 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
rhodesa Posted December 16, 2008 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.