[RESOLVED] Problems with a sql query picking up a variable

[cainy1982]

Not sure if anyone can tell me what I am going wrong.

<?php // allow the user to add a topic in the forum
//set the page title
$page_title = 'Forum - Delete Post';

// include the configuation file
require_once ('../includes/config.inc.php');

// include the header
include ('../includes/header.html');

// get value of hidden from url
$reply_id=$_GET['rid'];

$topic_id=$_GET['tid'];

// if no session variable is present (i.e the user is not logged in re direct to the homepage

if (!isset($_SESSION['user_id']) or ($_SESSION['user_level']) == 0) {

	// session variable isnt set so set the re direct url

	// free all memory assoctaed with the query
	mysqli_free_result($r);

	//close the $dbc connection
	mysqli_close($dbc);

	// set the url using the defult host in the config settings
	$url = BASE_URL . 'index.php';

	// use the header commend to re direct to the URl crated above
	header("Location: $url");

	//terminate the current script
	exit();

}

// require the mysql connection from the config file
require_once (MYSQL);


// Check if the form has been submitted:
if (isset($_POST['submitted'])) {

if ($_POST['sure'] == 'Yes') { // Delete the record.

	// Make the query:
$q ="DELETE FROM forum_reply WHERE reply_id='$reply_id'";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// check the query ran by seeing how many rows have been affected
if (mysqli_affected_rows($dbc) == 1)

	 { // If it ran OK.

		// Print a message:
		echo '<p>The user has been deleted.</p>';	

	} else { // If the query did not run OK.
		echo '<p class="error">The user could not be deleted due to a system error.</p>'; // Public message.
		echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
	}

} else { // No confirmation of deletion.
	echo '<p>The user has NOT been deleted.</p>';	
}

} else { // Show the form.


// get all data from the table using the hidden value pass through on the form	
echo $q2="SELECT * FROM forum_reply WHERE reply_id='$reply_id'";

// For debugging return any errors or run the query 
$r2 = mysqli_query ($dbc, $q2) or trigger_error("Query: $q2\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_num_rows($r2) == 1) { // Valid user ID, show the form.

// Get the reply information
while ($row = mysqli_fetch_array ($r2));

	// Create the form:
	echo '<form action="forum_delete_reply.php" method="post">
<h3>Reply: ' . $reply . '</h3>
<p>Are you sure you want to delete this reply?<br />
<input type="radio" name="sure" value="Yes" /> Yes 
<input type="radio" name="sure" value="No" checked="checked" /> No</p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="id" value="' . $topic_id . '" />
</form>';

} else { // Not a valid user ID.
	echo '<p class="error">This page has been accessed in error.</p>';
}

} // End of the main submission conditional.


include ('../includes/footer.html');
?>

When the code runs and the form is posted I get the messag ebelow

The user could not be deleted due to a system error.

Query: DELETE FROM forum_reply WHERE reply_id=''

I just cant work out why the variable $reply_id is not appearing

I know the variable is workintg because if the frm has not been submitted I get the text below

SELECT * FROM forum_reply WHERE reply_id='51'
Reply:

Are you sure you want to delete this reply?
Yes No

Any help, pushes or shoves welcome!!

[dagon]

your not submitting the value with the conformation form, and your checking the get array when your form action is post.

[cainy1982]

Great tha work a treat and makes sense.

been trying forhours to resolve this.

Completed code below for info incase anyone gets the same problem

<?php // allow the user to add a topic in the forum
//set the page title
$page_title = 'Forum - Delete Post';

// include the configuation file
require_once ('../includes/config.inc.php');

// include the header
include ('../includes/header.html');

// get value of hidden from url
$reply_id=$_GET['rid'];

$topic_id=$_GET['tid'];

// if no session variable is present (i.e the user is not logged in re direct to the homepage

if (!isset($_SESSION['user_id']) or ($_SESSION['user_level']) == 0) {

	// session variable isnt set so set the re direct url

	// free all memory assoctaed with the query
	mysqli_free_result($r);

	//close the $dbc connection
	mysqli_close($dbc);

	// set the url using the defult host in the config settings
	$url = BASE_URL . 'index.php';

	// use the header commend to re direct to the URl crated above
	header("Location: $url");

	//terminate the current script
	exit();

}

// require the mysql connection from the config file
require_once (MYSQL);


// Check if the form has been submitted:
if (isset($_POST['submitted'])) {

if ($_POST['sure'] == 'Yes') { // Delete the record.

$reply_id=$_POST['rid'];

$topic_id=$_POST['tid'];

	// Make the query:
$q ="DELETE FROM forum_reply WHERE reply_id='$reply_id'";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

// check the query ran by seeing how many rows have been affected
if (mysqli_affected_rows($dbc) == 1)

	 { // If it ran OK.

	// free all memory assoctaed with the query
	mysqli_free_result($r);

	//close the $dbc connection
	mysqli_close($dbc);

	// set the url using the defult host in the config settings
	$url = BASE_URL . "forum_view_topic.php?id=$topic_id";

	// use the header commend to re direct to the URl crated above
	header("Location: $url");

	//terminate the current script
	exit();

	} else { // If the query did not run OK.
		echo '<p class="error">The user could not be deleted due to a system error.</p>'; // Public message.
		echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
	}

} else { // No confirmation of deletion.
	echo '<p>The user has NOT been deleted.</p>';	
}

} else { // Show the form.


// get all data from the table using the hidden value pass through on the form	
echo $q2="SELECT * FROM forum_reply WHERE reply_id='$reply_id'";

// For debugging return any errors or run the query 
$r2 = mysqli_query ($dbc, $q2) or trigger_error("Query: $q2\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_num_rows($r2) == 1) { // Valid user ID, show the form.

// Get the reply information
while ($row = mysqli_fetch_array ($r2));

	// Create the form:
	echo '<form action="forum_delete_reply.php" method="post">
<h3>Reply: ' . $reply . '</h3>
<p>Are you sure you want to delete this reply?<br />
<input type="radio" name="sure" value="Yes" /> Yes 
<input type="radio" name="sure" value="No" checked="checked" /> No</p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="tid" value="' . $topic_id . '" />
<input type="hidden" name="rid" value="' . $reply_id . '" />
</form>';

} else { // Not a valid user ID.
	echo '<p class="error">This page has been accessed in error.</p>';
}

} // End of the main submission conditional.


include ('../includes/footer.html');
?>

[bradgrafelman]

Also note that the above code is vulnerable to SQL injection attacks and/or just plain SQL errors. User-supplied data should never be placed directly into a SQL query string; instead it must first be sanitized with a function such as [man]mysqli_real_escape_string/man (for string data).

[cainy1982]

Thanks for the I usually add that ($fn = mysqli_real_escape_string ($dbc, $trimmed['first_name'])😉 when taking data from forms but I cant see where to insert it when deleting data.

The form above wont actually take any data it relied in hidden form fields to run the queries.

Can you show me what you mean?

[bradgrafelman]

cainy1982 wrote:

Thanks for the I usually add that ($fn = mysqli_real_escape_string ($dbc, $trimmed['first_name']) when taking data from forms but I cant see where to insert it when deleting data.

It doesn't matter what type of query you're executing - the process is the same. Instead of directly referring to the user-supplied data, first sanitize it and insert that into the SQL query string instead.

Another way I like to form SQL queries is using [man]sprintf/man, e.g.:

$sql = sprintf(
    "SELECT col1, col2 FROM foo WHERE id = %d OR bar='%s'",
    $_POST['id'],
    mysqli_real_escape_string($dbc, $_POST['bar'])
);

cainy1982 wrote:

The form above wont actually take any data it relied in hidden form fields to run the queries.

There's no difference between a text field in a form and a hidden form field; both are user-supplied data that can easily be manipulated into whatever the user likes.

[cainy1982]

Thanks for the advice, for anyone interested my updated code is below including cleaning the data before processing it in SQL.

<?php // allow the user to add a topic in the forum
//set the page title
$page_title = 'Forum - Delete Post';

// include the configuation file
require_once ('../includes/config.inc.php');

// include the header
include ('../includes/header.html');

// if no session variable is present (i.e the user is not logged in re direct to the homepage

if (!isset($_SESSION['user_id']) or ($_SESSION['user_level']) == 0) {

	// session variable isnt set so set the re direct url

	// free all memory assoctaed with the query
	mysqli_free_result($r);

	//close the $dbc connection
	mysqli_close($dbc);

	// set the url using the defult host in the config settings
	$url = BASE_URL . 'index.php';

	// use the header commend to re direct to the URl crated above
	header("Location: $url");

	//terminate the current script
	exit();

}

// require the mysql connection from the config file
require_once (MYSQL);

// get value of hidden from url and clean
$reply_id = mysqli_real_escape_string ($dbc, trim($_GET['rid']));
$topic_id = mysqli_real_escape_string ($dbc, trim($_GET['tid']));


// Check if the form has been submitted:
if (isset($_POST['submitted'])) {

if ($_POST['sure'] == 'Yes') { // Delete the record.

// get the variables posted from the form and clean
$reply_id = mysqli_real_escape_string ($dbc, trim($_POST['rid']));
$topic_id = mysqli_real_escape_string ($dbc, trim($_POST['tid']));

	// Make the query:
$q ="DELETE FROM forum_reply WHERE reply_id='$reply_id'";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));


// check the query ran by seeing how many rows have been affected
if (mysqli_affected_rows($dbc) == 1)

	 { // If it ran OK.

	// free all memory assoctaed with the query
	mysqli_free_result($r);

	//close the $dbc connection
	mysqli_close($dbc);

	// set the url using the defult host in the config settings
	$url = BASE_URL . "forum_view_topic.php?id=$topic_id";

	// use the header commend to re direct to the URl crated above
	header("Location: $url");

	//terminate the current script
	exit();

	} else { // If the query did not run OK.
		echo '<p class="error">The user could not be deleted due to a system error.</p>'; // Public message.
		echo '<p>' . mysqli_error($dbc) . '<br />Query: ' . $q . '</p>'; // Debugging message.
	}

} else { // No confirmation of deletion.
	echo '<p>The user has NOT been deleted.</p>';	
}

} else { // Show the form.


// get all data from the table using the hidden value pass through on the form	
echo $q2="SELECT * FROM forum_reply WHERE reply_id='$reply_id'";

// For debugging return any errors or run the query 
$r2 = mysqli_query ($dbc, $q2) or trigger_error("Query: $q2\n<br />MySQL Error: " . mysqli_error($dbc));

if (mysqli_num_rows($r2) == 1) { // Valid user ID, show the form.

// Get the reply information
 while ($rows=mysqli_fetch_array($r2, MYSQLI_ASSOC)){
	?>

<!-- Create the form:-->
<form action="forum_delete_reply.php" method="post">
<h3>Reply: <?php echo $rows['reply']; ?></h3>
<p>Are you sure you want to delete this reply?<br />
<input type="radio" name="sure" value="Yes" /> Yes 
<input type="radio" name="sure" value="No" checked="checked" /> No</p>
<p><input type="submit" name="submit" value="Submit" /></p>
<input type="hidden" name="submitted" value="TRUE" />
<input type="hidden" name="tid" value="<?php echo $topic_id ; ?>" />
<input type="hidden" name="rid" value="<?php echo $reply_id ; ?>" />
</form>
<?php
}
} else { // Not a valid user ID.
	echo '<p class="error">This page has been accessed in error.</p>';
}

} // End of the main submission conditional.


include ('../includes/footer.html');
?>