Insert html code into a mysql/mariadb field

General PHP Help
#1

I want to be able to insert html code into a mysql/mariadb field. Security is not an issue. It is a local network.

number is longtext
Here is my table:

CREATE TABLE `formdata` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `ip` varchar(64) NOT NULL,
  `timein` varchar(32) NOT NULL,
  `datein` varchar(32) NOT NULL,
  `name` longtext NOT NULL,
  `number` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=69 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Here is my php code:

<form  method="post">

&nbsp;	Miracle<input type="text" name="verse" maxlength="256"> 
<br />

<textarea rows="20" cols="200" name="content"  maxlength="2000">
</textarea>

<button type="submit">Submit</button>
</form>


<?php  
if(empty($_POST['verse'])){  $name_input = "verse";  } 
	else {	$verse_input = 	$_POST['verse'];	}
	
if(empty($_POST['content'])){  $content_input = "content" } 
	else {	$content_input = 	$_POST['content'];	}
	
	echo "DEBUG FORM $name_input : $number_input<br /> ";
if ( $name_input != "verse" ){
//sql input	*******************************	
	
	 $connection = new mysqli($host,$username, $password,$database);

 
	$dbname = "stream";
	mysqli_select_db( $connection,  $database);

	$result = mysqli_query ($connection,"SELECT CURDATE();");
	$row = mysqli_fetch_row($result);
	$date = $row[0];
   
	$result = mysqli_query ($connection, "SELECT CURTIME();");
	$row = mysqli_fetch_row($result);
	$time = $row[0];

	$str0 = '';
	$str1 = $_SERVER['REMOTE_ADDR'];
	$str2 = $time;
	$str3 = $date;
	$str4 = $verse_input;  
	$str5 =  $content_input;
$query = "insert into stream.formdata (`id`, `ip`, `timein`, `datein`, `verse`, `content`) VALUES (NULL ,'$str1','$str2','$str3','$str4','$str5');";


	echo "$query";
	$result = @ mysqli_query ($connection,$query)  or showerror();
		
		mysqli_close($connection);

}
?>

Here is sample data for input.

<br />John 21
<br />Jesus Appears to Seven Disciples
<br />1 After this Jesus revealed himself again to the disciples by the Sea of Tiberias, and he revealed himself in this way. 
<br />2 Simon Peter, Thomas (called the Twin), Nathanael of Cana in Galilee, the sons of Zebedee, and two others of his disciples were together. 
<br />3 Simon Peter said to them, "I am going fishing." They said to him, "We will go with you." They went out and got into the boat, but that night they caught nothing.
<br />4 Just as day was breaking, Jesus stood on the shore; yet the disciples did not know that it was Jesus. 
<br />5 Jesus said to them, <span class="red">"Children, do you have any 
fish?"</span> They answered him, "No." 
<br />6 He said to them, <span class="red">"Cast the net on the right side of 
the boat, and you will find some." </span> So they cast it, and now they were not able to haul it in, because of the quantity of fish. 
<br />7 That disciple whom Jesus loved therefore said to Peter, "It is the Lord!" When Simon Peter heard that it was the Lord, he put on his outer garment, for he was stripped for work, and threw himself into the sea. 
<br />8 The other disciples came in the boat, dragging the net full of fish, for they were not far from the land, but about a hundred yards off.
<br />9 When they got out on land, they saw a charcoal fire in place, with fish laid out on it, and bread. 
<br />10 Jesus said to them, <span class="red">"Bring some of the fish that 
you have just caught." </span>
<br />11 So Simon Peter went aboard and hauled the net ashore, full of large fish, 153 of them. And although there were so many, the net was not torn. 
<br />12 Jesus said to them, "Come and have breakfast." Now none of the disciples dared ask him, "Who are you?" They knew it was the Lord. 
<br />13 Jesus came and took the bread and gave it to them, and so with the fish. 
<br />14 This was now the third time that Jesus was revealed to the disciples after he was raised from the dead.
<br />
#2

Here are some points for the database table -

  1. You should have a single datetime field, that’s a standard DATETIME data type.
  2. You should use the InnoDB database engine.
  3. You should use the utf8mb4 character set.
  4. The name and number columns in your table definition don’t match the columns you are using in the INSERT query.

Here are a bunch of points for the posted code -

  1. The code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document.
  2. You need to validate the resulting web page at validator.w3.org
  3. You are outputting a newline character inside the textarea markup, which will be submitted as part of the data. You should put the closing </textarea> tag immediately after the closing > of the opening <textarea> tag.
  4. The post method form processing code needs to detect if a post method form was submitted before referencing any of the form data.
  5. You need to keep the form data as a set, in a php array variable, then operate on elements in this array variable throughout the rest of the code.
  6. You need to trim all input data, mainly so that you can detect if all white space characters were entered, before validating it.
  7. You need to validate all input data before using it, storing user/validation errors in an array using the field name as the array index.
  8. If after validating all the input data, there are no user/validation errors, use the input data.
  9. If after using the input data, there are no user/validation errors, perform a redirect to the exact same URL of the current page to cause a get request for that page. This will prevent the browser from trying to resubmit the form data should that page get browsed back to or reloaded.
  10. If you want to display a one-time success message, either store the message or a flag value in a session variable, then test for the session variable, display the success message, and clear the session variable at the appropriate location in the html document.
  11. If there are user/validation errors, the code will continue on to (re)display the html document, where you will test for and display any user/validation errors, either all at once or adjacent to each field they correspond to, and redisplay the form, populating the fields with any existing data so that the user only needs to correct any errors and can resubmit the form, without needing to reenter all the values.
  12. Any dynamic value you output in a html context needs to have htmlentities() applied to it to prevent any html entity in a value breaking the html markup.
  13. Don’t copy variables to other variables for nothing. This is just a waste of typing. Just use the original variable that data is in.
  14. Don’t use multiple names for the same piece of data. This is just making more work keeping track of the names.
  15. The database connection statement is already selecting the database. There’s no point in the $dbname assignment (which isn’t being used) or in the mysqli_select_db() statement.
  16. There’s no need to query to get the CURDATE() and CURTIME() values. You can directly use any database value in the INSERT query.
  17. Only include columns in the insert query that you are suppling data for, i.e. leave out the id column and the corresponding null value.
  18. As already mentioned, there should be a single datetime column that’s a standard DATETIME datatype. You can use NOW() in the INSERT query to reference the current datetime value on the database server.
  19. You must provide protection against sql special characters in a value breaking the sql query syntax, which likely the problem you are currently having. The simple way of doing this, for all data types, is to use a prepared query. If it seems like using the mysqli extension is overly complicated and inconsistent, especially when dealing with prepared queries, it is. This would be a good time to switch to the much simpler and better designed PDO extension.
  20. Don’t use the @ error suppressor, ever.
  21. Modern php (8+) uses exceptions for database errors by default. When using exceptions, no discrete error handling logic, such as the or showerror(), will get executed upon an error and should be removed.
  22. There’s generally no need to close database connections in your code since php destroys all resources when your script ends.
  23. You need to decide what the code should do for duplicate data values, such as an existing verse. The verse column needs to be defined as a unique index. If you want to insert a new verse or update an existing one, use an INSERT ... ON DUPLICATE KEY UPDATE ... query. If you want to produce an error message for the user if a verse already exists, you would have exception try/catch logic for the execution of this query, that tests for a duplicate index error number, and sets up a message for the user letting them know that the verse already exists.
1 Like
#3

Here’s an example showing the points that have been made -

/*
CREATE TABLE `formdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(64) NOT NULL,
  `created` datetime NOT NULL,
  `verse` longtext NOT NULL,
  `content` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `verse` (`verse`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
*/

// 1) initialization
session_start();

// this example uses the PDO extension
// make the database connection
require 'pdo_connection.php';

$post = []; // array to hold a trimmed working copy of the form data
$errors = []; // array to hold user/validation errors

// 2) post method form processing
if($_SERVER['REQUEST_METHOD'] === 'POST')
{
	// trim all input data
	$post = array_map('trim',$_POST); // if any input is an array, use a recursive trim function here instead of php's trim
	
	// validate all input data
	if($post['verse'] === '')
	{
		$errors['verse'] = 'Verse is required.';
	}
	if($post['content'] === '')
	{
		$errors['content'] = 'Content is required.';
	}
	
	// if no errors, use the input data
	if(empty($errors))
	{
		$sql = "INSERT INTO formdata (`ip`, `created`, `verse`, `content`) VALUES
		(?, NOW(), ?, ?)";
		$stmt = $pdo->prepare($sql);
		try { // a 'local' try/catch to handle a specific error
			$stmt->execute([ $_SERVER['REMOTE_ADDR'], $post['verse'], $post['content'] ]);
		} catch (PDOException $e) {
			if($e->errorInfo[1] != 1062) // duplicate index error number
			{
				throw $e; // re-throw the exception if not handled by this logic
			}
			// this example is for a single unique column, verse
			$errors['verse'] = "The Verse already exists.";
		}
	}
	
	// if no errors, success
	if(empty($errors))
	{
		$_SESSION['success_message'] = 'Data has been inserted.';
		// redirect to the exact same URL of the current page to cause a get request - Post, Redirect, Get (PRG)
		die(header("Refresh:0"));	
	}
}

// 3) get method business logic - get/produce data needed to display the page

// 4) html document
// only the parts necessary for this example are shown
?>

<?php
// display any success message
if(!empty($_SESSION['success_message']))
{
	echo "<p>".htmlentities($_SESSION['success_message'])."</p>";
	unset($_SESSION['success_message']);
}
?>

<?php
// display any errors
if(!empty($errors))
{
	$er = array_map('htmlentities',$errors);
	echo "<p>".implode('<br>',$er)."</p>";
}
?>

<?php
// display the form
?>
<form  method="post">
&nbsp;	Miracle<input type="text" name="verse" maxlength="256" value='<?=htmlentities($post['verse']??'')?>'><br>
<textarea rows="20" cols="200" name="content"  maxlength="2000"><?=htmlentities($post['content']??'')?></textarea>
<button type="submit">Submit</button>
</form>
#4

Thank you for cleaning up my code! This really helps. I my code to work. I can now insert text with html code and single quotes.

Sponsor our Newsletter | Privacy Policy | Terms of Service