Database Not Updating - Retrieving Just Fine

[vicdesigns]

Hi guys,

 

Been a while since I posted on the forums (if at all).

 

Have an issue that's been biting me for about two days. It's probably staring me in the face though.

I can display data from MySQL with PHP on the page just fine. However, updating the database via a form is just not happening. The form just reverts to display the data that was manually entered into the database or the database table is emptied upon submit.

 

Here is the PHP being used to retrieve and update:

 

// Update Database Tables
$result = mysql_query("UPDATE `dev_cms`.`settings` SET `site_name` = '$site_name',
`site_slogan` = '$site_slogan',
`admin_email` = '$admin_email',
`facebook` = '$facebook',
`twitter` = '$twitter',
`tos` = '$tos' WHERE `settings`.`id` =1;");
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM settings")
or die(mysql_error());
// store the record of the settings table into $row
$row = mysql_fetch_array( $result );

 

The form:

 

		 <form action="settings.php" method="post">
			 <table class="listing form" cellpadding="0" cellspacing="0">
				 <tr>
					 <th class="full" colspan="2">General Settings</th>
				 </tr>
				 <tr>
					 <td width="172"><strong>Site Name</strong></td>
					 <td><input type="text" name="site_name" class="text" value="<?php echo $row['site_name']; ?>"/> <i>Your website Name</i></td>
				 </tr>
				 <tr>
					 <td><strong>Site Slogan</strong></td>
					 <td><input type="text" name="site_slogan" class="text" value="<?php echo $row['site_slogan']; ?>"/> <i>A catchy slogan</i></td>
				 </tr>
				 <tr>
					 <td><strong>Admin Email</strong></td>
					 <td><input type="text" name="admin_email" class="text" value="<?php echo $row['admin_email']; ?>"/> <i>For outgoing email</i></td>
				 </tr>
				 <tr>
					 <td><strong>Facebook Page</strong></td>
					 <td><input type="text" name="facebook" class="text" value="<?php echo $row['facebook']; ?>"/> <i>Your Facebook address</i></td>
				 </tr>
				 <tr>
					 <td><strong>Twitter ID</strong></td>
					 <td><input type="text" name="twitter" class="text" value="<?php echo $row['twitter']; ?>"/> <i>Your Twitter ID</i></td>
				 </tr>
				 <tr>
					 <td><strong>Terms of Service</strong></td>
					 <td><textarea name="tos" border="0" cols="45" rows="5"><?php echo $row['tos']; ?> </textarea><i>Terms and Conditions</i></td>
				 </tr>
				 <tr>
					 <td>				 <label>
				 <input type="submit" name="Submit" value="Save" />
			 </label></td>
				 </tr>
			 </table>
			 </form>

 

If anyone could please, without too much Jargon, have a look and point me in the right direction I would greatly appreciate it.

 

Thanks in advance.

[Christian F.]

We'll need to see all of your PHP code on the page that processes the form input.

[vicdesigns]

Sure. connection.php as follows:

 

<?php
// Start the session

@session_start();

require_once('config.php');

// Setup connection
$database_connection = @mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD) or die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check database connection parameters!"));
@mysql_select_db(DATABASE_NAME, $database_connection) or die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check your database exists!"));

// Update Database Tables
$result = mysql_query("UPDATE `dev_cms`.`settings` SET `site_name` = '$site_name',
`site_slogan` = '$site_slogan',
`admin_email` = '$admin_email',
`facebook` = '$facebook',
`twitter` = '$twitter',
`tos` = '$tos' WHERE `settings`.`id` =1;");

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM settings")
or die(mysql_error());  

// store the record of the settings table into $row
$row = mysql_fetch_array( $result );

?>

 

Thanks.

[Christian F.]

Sure. connection.php as follows...

Hmm... I think I see the problem:

<form action="settings.php...

 

Also, you should check if the form has indeed been submitted, by using if (!isset ($_POST['Submit'])) { die ('Form not submitted'); }

Secondly, you have no input validation or output escaping in your script. Which makes you completely open to both SQL injections and HTML injection (XSS, etc) attacks. You really should look into input validation, as well as mysql_real_escape_string () (for SQL) and htmlspecialchars () (for HTML).

[vicdesigns]

Nah that's not it. The settings.php is where the form is located. The connection.php is where the form data is processed through. That file is called in settings.php via require_once() etc.

I have also moved the code from connection.php to settings.php to see if it will load from there and same results.

 

Regarding the vulnerabilities, thanks for that. Yep, these will be done. Just developing at this time.

[jazzman1]

Your ternary operator syntax is wrong. You cannot use it to assign two values as you are attempting to do.

 

EDIT: The syntax is maybe okay, but you cannot use it in this way.....

Edited October 29, 2012 by jazzman1

[Christian F.]

Nothing wrong with the ternary operator, or how he's using it. He's not trying to assign two values either, but to display one of two error messages depending upon the environment the code is running in.

[jazzman1]

@Christian, don't you see something wrong here:

@mysql_select_db(DATABASE_NAME, $database_connection) or die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check your database exists!"));

[Christian F.]

Nope, I don't:

php > define ('SITE_MODE', 'normal');
php > die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check your database exists!"));
An error occured! Please check your database exists!

[jazzman1]

Yep, you are right. It's my mistake..

[Barand]

I can't see where you are picking up the values from the POST data anywhere in your code.

[vicdesigns]

Hi Barand,

 

Thank you for that. I think you are rigjht. It isn't something I am familiar with but I have given it a shot from what I could find on google.

 

Here is the modified connection.php file. When the form is submitted now it blanks the form and sets all the variables in the actual URL.

 

<?php

// Start the session

@session_start();

require_once('config.php');

// Protect against MySQL Injection
function ExtendedAddslash(&$params)
{
    foreach ($params as &$var) {
	    // check if $var is an array. If yes, it will start another ExtendedAddslash() function to loop to each key inside.
	    is_array($var) ? ExtendedAddslash($var) : $var=addslashes($var);
	    unset($var);
    }
}

// Initialize ExtendedAddslash() function for every $_POST variable
$id = $_POST['id'];
$site_name = $_POST['site_name'];
$site_slogan = $_POST['site_slogan'];
$admin_email = $_POST['admin_email'];
$site_offline = $_POST['site_offline'];
$facebook = $_POST['facebook'];
$twitter = $_POST['twitter'];
$tos = $_POST['tos'];    

// Setup connection
$database_connection = @mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD) or die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check database connection parameters!"));

@mysql_select_db(DATABASE_NAME, $database_connection) or die(((SITE_MODE == "development") ? mysql_error() : "An error occured! Please check your database exists!"));

// Retrieve all the data from the "example" table

$query = "SELECT * FROM `settings` WHERE `id` = '$id'";
$sqlsearch = mysql_query($query);
$resultcount = mysql_numrows($sqlsearch);

if ($resultcount > 0) {

   mysql_query("UPDATE `settings` SET
						    `site_name` = '$site_name',
						    `site_slogan` = '$site_slogan',
						    `admin_email` = '$admin_email',
						    `site_offline` = '$site_offline',
						    `facebook` = '$facebook',
						    `twitter` = '$twitter',
						    `tos` = '$tos'     
						 WHERE `id` = '$id'")
 or die(mysql_error());

}
?>

 

Any guidance is appreciated.

 

Cheers.