Updating Records

[monstaface]

I am fairly new to PHP and am creating a website for a charity where it allows staff members to view and update victim records that are stored in the SQL Database via the website. I have managed to display the records and create a form that allows the alteration of a single records on the new page which is what I require.
 
However when I alter the information within the form and select the button to lead me to the next page where my 'Update' code is, I receive the statement that the record has been updated when it hasn't.
 
My update code:

 

$updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."' WHERE victimId='".mysql_real_escape_string($_POST['victimId'])."'";
$exeupdateSQL=mysql_query($updateSQL) or die (mysql_error());
echo "The Record has been updated";
echo "<br><a href=ViewVictimRequest.php>View Updated Record</a>";

However when I remove the variables and place data within the query the records become updated and it works, for example:

$updateSQL="UPDATE Victims SET victimFName='Mary Smith' WHERE victimId='1'";
      $exeupdateSQL= mysql_query($updateSQL) or die (mysql_error());
      echo "The Record has been updated";

The issue lies within my variables, however I am unsure on how to fix this problem. Thank you in advance for your help!

 

 

 

[jazzman1]

Have you checked the values (on the server-side) of "victimFName" and "victimId" when the form is being submitted?

[monstaface]

Sorry could you elaborate on what you mean?

[monstaface]

I have established that there is an issue with the posting of the variable, I would be grateful if anyone could please have a look and explain how I can fix this issue as I just can't seem to figure it out 

 

Thank you!  

[Ch0cu3r]

jazzman1 means have you checked that the $_POST['victimFName] and $_POST['victimId] variables contains the values you expect when the form has been submitted. You can verify this by dumping the contents of $_POST by using

printf('<pre>%s</pre>', print_r($_POST, true));

Just because mysql_query did not return false does not mean the query actually did anything. You should be using mysql_affected_rows to check to see if an update query actually did anything. Also take note of the big red message box on that manual page. You should upgrade your code to either MySQLi (note the i ) or PDO function libraries.

[jazzman1]

Believe or not, the ability of debugging your applications is an essential part of every development and this process shoud be learn from every programmer after he/she type echo "Hello World" and print the output to the browser.Everything you need to do is to validate in a proper/security way every value coming from your update form and going to the application and database server later. So, post your html update form and at a starting point before sending a query to database server, just echo the statement and post the result.

$updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."' WHERE victimId='".mysql_real_escape_string($_POST['victimId'])."'";

echo $updateSQL; exit; 

[monstaface]

echo $_POST['victimFName']; gave me a blank space

 

and echo $updateSQL displays:

 

 

 

UPDATE Victims SET victimFName='' WHERE victimId=''

 

This implies that my variable is empty, i'm confused as I defined my variable within the post. I know I sound extremely stupid but how do I fix this?

 

Sorry!

[Ch0cu3r]

What/Where is your form? Are you sure you have set the forms submit method to post and not get (which is the default)

[monstaface]

This is my form:

<?php
session_start();
include ("db.php");
//create a variable called $pagename which contains the actual name of the page
$pagename="Update Form";

//call in the style sheet called ystylesheet.css to format the page as defined in the style sheet
echo "<link rel=stylesheet type=text/css href=mystylesheet.css>";

//display window title
echo "<title>".$pagename."</title>";
//include head layout 
include("headlayout.html");

//display the name of the site and the date dynamically. See uk.php.net
echo date ('l d F Y H:i:s');
echo "<p></p>";
//display name of the page
echo "<h2>".$pagename."</h2>";

$id=$_GET['victimId'];

{$SQL="select victimId, victimFName,victimSName,victimTelNo,contactName,victimEmail,victimPostCode,victimIssueDescription from Victims where victimId='$id'";
$exeSQL=mysql_query($SQL) or die (mysql_error());
$arrayvictim=mysql_fetch_array($exeSQL);

echo "<form method=post action=geteditform.php>" ;
echo "<input type=hidden name=id value=$arrayvictim[victimId]>";
echo "<table border=0 cellpadding=5>";
echo "<tr><td>First Name </td>";
echo "<td><input type=text name=r_firstname size=35 value=$arrayvictim[victimFName]></td></tr>";
echo "<tr><td>Last Name </td>";
echo "<td><input type=text name=r_lastname size=35 value=$arrayvictim[victimSName]></td></tr>";
echo "<tr><td>Requester Name </td>";
echo "<td><input type=text name=r_contactname size=35 value=$arrayvictim[contactName]></td></tr>";
echo "<tr><td>Tel No </td>";
echo "<td><input type=text name=r_telno size=35 value=$arrayvictim[victimTelNo]></td></tr>";
echo "<tr><td>Email Address </td>";
echo "<td><input type=text name=r_email size=35 value=$arrayvictim[victimEmail]></td></tr>";
echo "<tr><td>Postcode </td>";
echo "<td><input type=text name=r_postcode size=35 value=$arrayvictim[victimPostCode]></td></tr>";
echo "<tr><td>Issue Description</td>";
echo "<td><TEXTAREA NAME=r_issuedescription ROWS=6 COLS=40 value=$arrayvictim[victimIssueDescription]>
</TEXTAREA></td></tr>";
echo "<tr><td><input type=submit value='Edit Form'></td>";

echo "</table>";
echo "</form>" ;
}  


//include head layout
include("footlayout.html");
?>  

Then I have the 'getedit.php' page:

session_start();
include ("db.php");

$updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."' WHERE victimId='".mysql_real_escape_string($_POST['victimId'])."'";
$exeupdateSQL=mysql_query($updateSQL) or die (mysql_error());
echo "The Record has been updated";
echo "<br><a href=ViewVictimRequest.php>View Updated Record</a>";

Now "The record has been updated" is shown but the record doesn't update. . 

Edited April 13, 2014 by monstaface

[Ch0cu3r]

$_POST['victimFName'] and $_POST['victimId'] should relate to the fields in your form.

 

You do not have fields named as victimFName or victimId. You should be using $_POST['r_firstname'] and $_POST['id'] instead.

$id=$_GET['victimId'];

You should be sanitizing this $_GET['victimId'] before using it in your query. Either use mysql_real_escape_string or use intval to protect against SQL injection.

[monstaface]

If I gifted you my first born child- that would not be thanks enough!

Thank you so so much.

 

Eternally grateful, and in happy tears while writing my code. May you be forever blessed!!

[jazzman1]

 

If I gifted you my first born child- that would not be thanks enough!

 

Cool

[monstaface]

I was wondering is it possible to remove my main form? There are no edit or delete buttons for my posts and the code is for a larger project

 

Thank you! 

[jazzman1]

Yes, you could delete and re-create it but make sure you have a backUp for every case

[monstaface]

I wanted to ask a question about my form, when I access that page the whole postcode doesn't show in the r_postcode box and only the first three netters/numbers do. Is there a way to fix this?

[Ch0cu3r]

Yes always wrap the value for the  value  attribute in quotes,

example for the firstname

echo "<td><input type=text name=r_firstname size=35 value=\"$arrayvictim[victimFName]\"></td></tr>";

The \ before the " means it is escaped.

[monstaface]

Thank you so much! 

[Jacques1]

No, not really. This is missing the point.

 

The value must be HTML-escaped before you can insert it into your HTML document. Never insert unescaped values. If you're lucky, this will only break your markup (which is what just happened). If you have less luck, an attacker will use this bug to inject malicious JavaScript code.

 

So always escape your input properly:

<?php

echo '<td><input type="text" name="r_firstname" size="35" value="' . html_escape($arrayvictim['victimFName']). '"></td></tr>';



// Put this into some global file.
function html_escape($raw_input)
{
	// If you're not using UTF-8, adjust this accordingly.
	return htmlspecialchars($raw_input, ENT_QUOTES, 'UTF-8');
}

[monstaface]

^Thanks! 

[monstaface]

Sorry one more question! The answer above helped when I had a normal textbox in the form, however when I use a text area the information doesn't show on the form from the database. Example:

 

echo "<tr><td>Life story</td>";

echo "<td><TEXTAREA NAME=r_lifestory ROWS=6 COLS=40 value=$arraylife[lifestory]>
</TEXTAREA></td></tr>";

[Jacques1]

Again: Escape and quote your attributes!

 

If you keep forgetting this, write it down on a post-it and stick it to your screen. This is really, really important.

 

Besides that, the textarea element has no value attribute. The (escaped!) content goes between the tags.

[monstaface]

^ Sorry! I'm going to do it now lol!

Edited April 15, 2014 by monstaface