Jump to content

mysql_real_escape_string when editing.

davidcriniti

By davidcriniti
in PHP Coding Help

 Share

Recommended Posts

davidcriniti Member

davidcriniti

Posted
Posted

Hi everyone,

 

I have a table that allows people to be added to a database (Firstname, lastname, sex and notes).

 

Had to tweak it a little so I could add names like "O'Brien" etc, by putting in a real escape string.

 

I can add these names ok now. However, when I go to edit one of these names, the edit does not complete. I've tried adding the real escape string to other parts of the code (just guesswork), but nothing I've done so far has worked. Does anyone have any ideas?

 

 

I presume the changes need to be made in this part of the code? :

 


<?php  

include 'mysql_connect.php'; 
  
$act = $_POST['act'];
switch($act){
   case 'save_new':
      // save for new action
      $firstname =  mysql_real_escape_string ($_POST["firstname"]);
      $lastname =  mysql_real_escape_string ($_POST["lastname"]);
      $sex       = strtoupper($_POST['sex']);
      $notes =  mysql_real_escape_string ($_POST["notes"]);
      $date_started       = date("Y/m/d h:i:s");
      $sql = "
         INSERT INTO t_athletes(firstname, lastname, sex, notes, date_started)
         VALUES('{$firstname}', '{$lastname}', '{$sex}', '{$notes}', '{$date_started}')
      ";
      if(mysql_query($sql)){
         $_SESSION['message'] = 'Add new entrant success';
      }else{
         $_SESSION['message'] = 'Add new entrant fail';
      }
      ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <?
      die();
      break;
   case 'delete_list':
      // delete action
      $cid = $_POST['cid'];
      if(count($cid)){
         $sql = "
            DELETE FROM t_athletes
            WHERE athlete_id IN('".implode("','", $cid)."')
         ";
         mysql_query($sql);
         $_SESSION['message'] = count($cid) . ' entrant(s) deleted';
         ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <?
         die();
      }
      break;
   case 'save_all':
      // save for edit action
      $cid      = $_POST['cid'];
      $ids      = $_POST['ids'];
      $firstnames = $_POST['firstnames'];
      $lastnames    = $_POST['lastnames'];
      $sexes       = $_POST['sexes'];
      $notess    = $_POST['notess'];
      $sql = "
         UPDATE t_athletes
         SET 
      ";
      if(count($cid))
         foreach($cid as $key => $val){
            $fields = array();
            $fields[] = "firstname='".$firstnames[$key]."'";
            $fields[] = "lastname='".$lastnames[$key]."'";
            $fields[] = "sex='".strtoupper($sexes[$key])."'";
            $fields[] = "notes='".$notess[$key]."'";
            mysql_query($sql . implode(",", $fields) . " WHERE athlete_id = '".$cid[$key]."'");
         }
      $_SESSION['message'] = 'Entrants edited success';   
      ?> <script type="text/javascript"> window.location = 'athlete_notes3.php'; </script> <?
      die();
      break;
}
?>

 

...but I'll paste the whole code for the page in case changes are needed elsewhere?

 

 


              <?php
// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM t_athletes")
or die(mysql_error());
?>
<form name="form" method="post"> 
<div style="text-align:center;color:#FF0000;">
<?php
echo $_SESSION['message'];
$_SESSION['message'] = '';
?>
</div>
<table width="100%">
   <tr>
       <td valign="top" align="center">
        <table style="display:none;" id="addNewForm">
           <tr>
               <td align="left">First Name</td>
                <td align="left"><input type="text" name="firstname" /></td>
            </tr>
            <tr>
               <td align="left">Last Name</td>
                <td align="left"><input type="text" name="lastname" /></td>
            </tr>
            <tr>
               <td align="left">Sex</td>
                <td align="left">
                   <select name="sex">
                       <option value="M">M</option>
                        <option value="F">F</option>
                    </select>
                </td>
            </tr>
            <tr>
               <td align="left">notes</td>
                <td align="left"><input type="text" name="notes" /></td>
            </tr>
            <tr>
               <td colspan="2">
                   <button type="button" onClick="save();">Save</button>
                    <button type="button" onClick="cancelAddNew();">Cancel</button>
                </td>
            </tr>
        </table>
        <button type="button" onClick="addNew();" id="btn_add">Add</button> 
        <button type="button" onClick="deleteList();" id="btn_delete">Delete</button> 
        <button type="button" onClick="editAll();" id="btn_edit_all">Edit All</button> 
        <button type="button" onClick="saveAll();" id="btn_save_all">Save All</button> 
        <button type="button" onClick="cancelAll();" id="btn_cancel_all">Cancel All</button>
        </td>
    </tr>
</table>
<table border="1" align="center" cellpadding="10" cellspacing="0" bordercolor="#000000" class="list" width="100%">
<tr bgcolor="#6175BE"><th>Delete</th> <th>firstname</th> <th>lastname</th> <th>sex</th> <th>notes</th><th>Edit</th></tr>
<?php
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
   // Print out the contents of each row into a table
   ?>
   <tr id="row_<?php echo $row['athlete_id'];?>" bgcolor='#CCCCCC'>
       <td width="30" align="center">
           <input type="checkbox" name="cid[<?php echo $row['athlete_id'];?>]" value="<?php echo $row['athlete_id'];?>" class="checkedbox" />
        </td>
       <td>
           <span><?php echo $row['firstname'];?></span>
            <input type="text" value="<?php echo $row['firstname'];?>" class="editable" name="firstnames[<?php echo $row['athlete_id'];?>]" />
        </td>
        <td width="200">
           <span><?php echo $row['lastname'];?></span>
            <input type="text" value="<?php echo $row['lastname'];?>" class="editable" name="lastnames[<?php echo $row['athlete_id'];?>]" />
        </td>
        <td width="50" align="center">
           <span><?php echo $row['sex'];?></span>
            <!--<input type="text" value="<?php echo $row['sex'];?>" class="editable" name="" />-->
            <select name="sexes[<?php echo $row['athlete_id'];?>]" class="editable">
               <option value="M" <?php echo ($row['sex'] == 'F') ? '' : 'selected="selected"';?>>M</option>
                <option value="F" <?php echo ($row['sex'] == 'M') ? '' : 'selected="selected"';?>>F</option>
            </select>
        </td>
      <td align="center" width="150">
           <span><?php echo $row['notes'];?></span>
            <textarea value="<?php echo $row['notes'];?>" class="editable" name="notess[<?php echo $row['athlete_id'];?>]" ></textarea>
        </td>
        <td width="60" align="center">
           <input type="hidden" value="<?php echo $row['athlete_id'];?>" class="rowid" name="ids[]" />
           <button type="button" onClick="javascript:editRow('<?php echo $row['athlete_id'];?>');" class="edit">Edit</button>
            <button type="button" class="save" onClick="saveRow('<?php echo $row['athlete_id'];?>')">Save</button>
            <button type="button" class="cancel" onClick="cancelRow('<?php echo $row['athlete_id'];?>');">Cancel</button>
      </td>
   </tr>   
    <?php
}?>
<!-- <tr>
       <td colspan="6" align="center">
        
        </td>
    </tr>-->
</table>
<input type="hidden" name="act" value="" />
</form>
<script>
// show form to add new entrant
function addNew(){
   var form = document.form;
   form.firstname.value = '';
   form.lastname.value = '';
   form.sex.value = 'm';
   form.notes.value = '';
   $("addNewForm").setStyles({display: 'block'});
   
   $("btn_add").setProperty("disabled", "disabled");
   $("btn_delete").setProperty("disabled", "disabled");
   $("btn_edit_all").setProperty("disabled", "disabled");
   $("btn_save_all").setProperty("disabled", "disabled");
   $("btn_cancel_all").setProperty("disabled", "disabled");
   
   $$("button.edit").setProperty("disabled", "disabled");
}

// hide form to add new entrant
function cancelAddNew(){
   $('addNewForm').setStyles({display: 'none'});
   
   $("btn_add").setProperty("disabled", "");
   $("btn_delete").setProperty("disabled", "");
   $("btn_edit_all").setProperty("disabled", "");
   
   $$("button.edit").setProperty("disabled", "");
}

// save new entrant
function save(){
   if(!checkInput()) return;
   document.form.act.value = 'save_new';
   document.form.submit();
}

// delete entrants
function deleteList(){
   var ids = $$("input.checkedbox");
   var selected = 0;
   for(var i=0;i<ids.length;i++){
      if(ids.checked) selected++;
   }
   if(selected==0){
      alert("Please select which record(s) you wish to delete");
      return;
   }
   if(!confirm("Are you sure you want to delete "+selected+" record(s) from this table")){
      return;
   }
   document.form.act.value = 'delete_list';
   document.form.submit();
}

// show row editable
function editRow(rowid){
   var row = $("row_"+rowid);
   row.editing = true;
   //row.getElements("span").setStyles({display: 'none'});
   //row.getElements("input.editable").setStyles({display: 'block'});
   
   var spans = row.getElements("span");
   var inputs = row.getElements(".editable");   
   for(var j = 0; j < spans.length; j++){
      spans[j].setStyles({display: 'none'});
      inputs[j].setStyles({display: 'block'});
      inputs[j].value = spans[j].innerHTML;
   }   
   
   row.getElements("button.edit").setStyles({display: 'none'});
   row.getElements("button.save").setStyles({display: 'block'});
   row.getElements("button.cancel").setStyles({display: 'block'});
   $$("button.edit").setProperty("disabled", "disabled");
   
   $("btn_add").setProperty("disabled", "disabled");
   $("btn_delete").setProperty("disabled", "disabled");
   $("btn_edit_all").setProperty("disabled", "disabled");
   $("btn_save_all").setProperty("disabled", "disabled");
   $("btn_cancel_all").setProperty("disabled", "disabled");
}

// hide row editable
function cancelRow(rowid){
   var row = $("row_"+rowid);
   row.editing = false;
   row.getElements("span").setStyles({display: 'block'});
   row.getElements(".editable").setStyles({display: 'none'});
   
   row.getElements("button.edit").setStyles({display: 'block'});
   row.getElements("button.save").setStyles({display: 'none'});
   row.getElements("button.cancel").setStyles({display: 'none'});
   
   $$("button.edit").setProperty("disabled", "");
   
   $("btn_add").setProperty("disabled", "");
   $("btn_delete").setProperty("disabled", "");
   $("btn_edit_all").setProperty("disabled", "");
   $("btn_save_all").setProperty("disabled", "disabled");
   $("btn_cancel_all").setProperty("disabled", "disabled");
}

// action for edit all rows
function editAll(){
   var ids = $$("input.rowid");
   for(var i=0;i<ids.length;i++){
      var row = $("row_"+ids.value);
      var spans = row.getElements("span");
      var inputs = row.getElements(".editable");   
      for(var j = 0; j < spans.length; j++){
         spans[j].setStyles({display: 'none'});
         inputs[j].setStyles({display: 'block'});
         inputs[j].value = spans[j].innerHTML;
      }      
   }
   $$("button.edit").setProperty("disabled", "disabled");
   $("btn_add").setProperty("disabled", "disabled");
   $("btn_delete").setProperty("disabled", "disabled");
   $("btn_edit_all").setProperty("disabled", "disabled");
   $("btn_save_all").setProperty("disabled", "");
   $("btn_cancel_all").setProperty("disabled", "");
}

// 
function cancelAll(){
   var ids = $$("input.rowid");
   for(var i=0;i<ids.length;i++){
      cancelRow(ids.value);
   }
   $("btn_add").setProperty("disabled", "");
   $("btn_delete").setProperty("disabled", "");
   $("btn_edit_all").setProperty("disabled", "");
   $("btn_save_all").setProperty("disabled", "disabled");
   $("btn_cancel_all").setProperty("disabled", "disabled");
}
function saveRow(rowid){
   var row = $("row_"+rowid);
   var checkboxes = row.getElements("input.checkedbox")[0];      
   checkboxes.checked = true;
   
   document.form.act.value = 'save_all';
   document.form.submit();
}
function saveAll(){
   var ids = $$("input.checkedbox");
   var ret = new Array(ids.length);
   var success =0, fail = 0;
   for(var i=0;i<ids.length;i++){
      ids.checked = true;
   }
   document.form.act.value = 'save_all';
   document.form.submit();
}

$("btn_save_all").setProperty("disabled", "disabled");
$("btn_cancel_all").setProperty("disabled", "disabled");


// make sure all fields are fill before save
function checkInput(){
   var form = document.form;
   if(form.firstname.value == ''){
      alert("Please enter firstname");
      form.firstname.focus();
      return false;
   }
   
   if(form.lastname.value == ''){
      alert("Please enter lastname");
      form.lastname.focus();
      return false;
   }
   
   if(form.notes.value == ''){
      alert("Please enter notes");
      form.notes.focus();
      return false;
   }
   return true;
}

</script>       

 

All advice is greatly appreciated.

 

Dave

 

MOD EDIT: code tags added.

Link to comment
https://forums.phpfreaks.com/topic/241273-mysql_real_escape_string-when-editing/
Share on other sites
requinix Prolific Member

requinix

Posted
Posted

The only time you should be using mysql_real_escape_string() is immediately before putting a string value into a query.

// early in your script you deal with the regular values
$name = $_POST["name"];
// $name = mysql_real_escape_string($_POST["name"]); -- bad
// here you can do some processing with $name - validate, santize, etc.

// ...

// later you actually insert it into the database
mysql_query("INSERT INTO table (name) VALUES ('" . mysql_real_escape_string($name) . "')");

For this to work properly, make sure you have the magic_quotes INI setting disabled.

 

Then, when you display the value on a page, you use a different function: htmlentities or htmlspecialchars. Same rules apply: only use the function immediately before you output the value.

$res = mysql_query("SELECT name FROM table");
$name = mysql_result($res, 0, 0);
// here you can do some other processing with $name - other queries, for instance
// (don't forget to use mysql_real_escape_string() if you do)

// ...

// later you display it
echo htmlentities($name);

These functions exist because you might not know what the value is. If you do - it's a valid date or number, for example - then you don't need to use either of them because you know that the data is safe.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.