Secure way to insert and get data from MySql using textarea and keep text unaltered?

PHP
1

Can someone tell what is the proper way to insert and get data from Mysql using textarea and keep text unaltered?
I mean linebreaks and backslash’s etc…
There is just way too many results in Google if I try to find simple quide or some example script for this.

I use mysqli_connect and here is example how I figured it so far;


$host = "localhost";
@$db = mysqli_connect(
$host,
'root',
'passwd',
'database');


// Here is how I insert data to Mysql table
// How do I secure $_POST[data] before saving it to Mysql?
mysqli_query($db, "INSERT INTO users_logged_table (mytext) VALUES ('$_POST[mytext]')");


// Here is how I get data from Mysql table
$query = mysqli_query($db, "SELECT mytext FROM mysql_table';");
$row = mysqli_fetch_array($query);
mysqli_free_result($query);


// textarea
// How do I print text exactly like user have typed it?
<textarea name="mytext">$row[mytext]</textarea>


// html
// How do I print text exactly like user have typed it?
echo $row[mytext];
2

It looks like you’re trying to take the data the user just typed from a completely different table than you just inserted it into.

If you insert the value into a table called users_logged_table, you can’t then get it back out of a table called mysql_table.

3

look at nl2br()

4

Sorry, it’s just a typo!

I just need to know what functions I need to secure save text from textarea to Mysql (using mysqli_connect) and bring text back (just like user typed) to textarea and html-code?

Btw, what is the difference using mysql_connect and mysqli_connect ?
Is mysqli_connect more secure?

Do I need something like?
nl2br()
stripslashes()
mysqli_real_escape_string()
get_magic_quotes_gpc()
etc… etc…

5

Well mysqli_real_escape_string will protect the database from the text being injectable. I highly recommend using it.

When getting the data back out, as rguy84 suggested, use nl2br().

Clearly you have some idea what you’re trying to do because you’ve named all of those functions so why not head on over to the manual at php.net and take some time to read up on them? - It can be a bit mind bending to start with but eventually it will click and when it does it will turn your programming world upside down :wink:

6

Thank you for advice, tangoforce :slight_smile:

So you mean like this?


$_POST[mytext] = mysqli_real_escape_string($db, $_POST[mytext]);

mysqli_query($db, "INSERT INTO mysql_table (mytext) VALUES ('$_POST[mytext]')");


// textarea
<textarea name="mytext">$row[mytext]</textarea>

$row[mytext] = nl2br($row[mytext]);

// html
echo $row[mytext];
7

I have use this script what I made a long time ago, but I really don’t know is it secure?


foreach($_POST as $input => $value) {
	if (!is_numeric($value)) {
	$_POST[$input] = mysqli_real_escape_string($db, $value);
	}
}
8

Similar yes. You still have some errors there though and you’re mixing php and html too. Let me re-work your code below and then you can see the difference:


<?
//This must be done AFTER connection to mysql is established
//Also note the single quotes.
$_POST['mytext'] = mysqli_real_escape_string($db, $_POST['mytext']);

//Looks good
mysqli_query($db, "INSERT INTO mysql_table (mytext) VALUES ('$_POST[mytext]')");


// textarea
//End your php code here
?>

<!--We're doing html now so we'll use small php code to print text-->
<textarea name="mytext"><? print $row['mytext']; ?></textarea>
<!--Surely though, this should be at the end of the script?-->

<?
//Back into php.

//Single quotes again - This has already been printed above in the html so why nl2br() it now?
$row['mytext'] = nl2br($row['mytext']);

// html
//Single quotes again
echo $row['mytext']; 
?>
9

Looks ok to me. I’m sure there are probably extra safe bullet proof methods but yes that is generally the simple and safe way most php coders recommend.

10

tangoforce, thank you for you example, but it does not work:
When I write this in textarea and submit:
row1
row2

row4

row6

After submit I get this:

in textarea:
row1\r
row2\r
\r
row4\r
\r
row6

html:
row1\r
row2\r
\r
row4\r
\r
row6

11

It seem’s that text’s prints correctly if I don’t use mysqli_real_escape_string
Why is that?

12

Because mysqli_real_escape_string ESCAPES any characters which could be dangerous to oinsert into the database.

If you have a \ in your string (such as \r
) then mysqi_real_escape_string will escape it to \\r\
so that the database ignores the original slashes (in case it is a sql injection attempt).

Also it depends on how the string is initialised with the \r
. EG:


//Doesn't work
$Test = 'This is a test\\r\
Testing';
$Test = nl2br($Test);

//This works
$Test = "This is a test\\r\
Testing";
$Test = nl2br($Test);

Since I’m not sure about the rest of the code in your script I can’t really advise further than to say you need to investigate where the string comes from. You could try something like


//No guarantee this will work at all.
$_POST["mytext"] = $_POST['mytext'];

Let me know how you get on. If it still doesn’t work download teamviewer and I’ll try to help you get it sorted with a desktop connection.