How to secure data for user insertion into DB

David_P

Is there a complete, yet simple way to implement mysql_real_escape_string and whatever else is necessary, to allow a user to submit data directly to the database and have some level of assurance that your server and website will not be destroyed?

I doubt my website will ever reach the level of Google or Yahoo, so I probably don't need the same level of security as they do. I just want to institute a small job board to use in my area.

It would be nice if a user could use some limited HTML like - , , and <li></li> tags, but if it isn't possible without the aid of add-on products or an extortionate amount of code, then I'll just forget about it.

I found this:

function safe_sql( $value )
{
$value = nl2br($value);
$value = trim(strip_tags($value,'<br>')); 
// Stripslashes
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value)) {
$value = mysql_real_escape_string($value);
}
return $value;
} // End of Function

I read that using - trim, strip_tags and mysql_real_escape_string is a good idea. The above function uses these so I would like to use it. How exactly would I use this function? Or rather how would I need to write all my field variables to take advantage of this function - $value?

bradgrafelman

David P wrote:
Is there a complete, yet simple way to implement mysql_real_escape_string and whatever else is necessary, to allow a user to submit data directly to the database and have some level of assurance that your server and website will not be destroyed?

Sounds like you should look into using [man]PDO[/man] (more specifically, [man]pdo-mysql[/man]) instead of the old, deprecated [man]MySQL[/man] functions.

David P wrote:
It would be nice if a user could use some limited HTML like - , , and <li></li> tags

Well, you already allow the BR element, so just add these other elements into the allowed elements list.

David P wrote:
I read that using - trim, strip_tags and mysql_real_escape_string is a good idea.

Always use mysql_real_escape_string() on user-supplied data (unless there are better methods, such as casting user-supplied data to an integer if that's what you're expecting; in this case, there's no need for mysql_real_escape_string()). As for [man]strip_tags/man... well that's up to you. Don't think that it's just another sanitizing function that you should always use, though - I've created several apps where I want the users to be able to input HTML, so using strip_tags() on incomming data wouldn't make sense. In addition, what if someone wanted to create a username (or subject, or whatever's applicable for your app) such as "<<<BradG>>>" ? In this case, you'd want to use [man]htmlentities/man when displaying the data.

David_P

So I'm OK if this is all I do:

<?php

include('includes/dbconnect.php');

$title = mysql_real_escape_string($_POST['title']);
$content = mysql_real_escape_string($_POST['content']);

$query = "INSERT INTO table VALUES ('', '$title', '$content')"; 
$result = mysql_query($query) or die('There was an error, please <a href=\"./../contact.php\">contact</a> the administrator.');
if ($result)

echo "<br><span style='color:red'><strong>Entry Added!</strong></span>";
footer();
exit();
mysql_close();
}

?>

So forget about addslashes since mysql_real_escape_string does it for you.

How about this on the front side:

<?php

include("includes/dbconnect.php");

$query="SELECT * FROM table ORDER BY id DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id = mysql_result($result,$i,'id');
$title = stripslashes(mysql_result($result,$i,'title'));
$content = nl2br(stripslashes(mysql_result($result,$i,'content')));

?>

Now I was doing this until I was told that it was a no-no:

$company = htmlspecialchars($_POST['company']);

if (!get_magic_quotes_gpc())
{

$company = addslashes($company);

}

<?php

include("includes/dbconnect.php");

$query="SELECT * FROM table ORDER BY id DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id = mysql_result($result,$i,'id');
$title = stripslashes(mysql_result($result,$i,'title'));
$title = htmlspecialchars_decode($title);

$content = nl2br(stripslashes(mysql_result($result,$i,'content')));
$content = htmlspecialchars_decode($content);

?>

And this being called through the functions page:

if ( !function_exists('htmlspecialchars_decode') )
{
    function htmlspecialchars_decode($text)
    {
        return strtr($text, array_flip(get_html_translation_table(HTML_SPECIALCHARS)));
    }
}

I thought that if you did not convert the <,>," and stuff to the HTML entities before putting them into the database that they would cause problems. Then you would translate them back to their original form when displaying to the public. I take it that this is indeed false?

If this is false, then why would I want to display this " instead of this " to the public? So I should just forget about the above function and

$title = htmlspecialchars_decode($title);

on the output altogether then?

MarkR

David P wrote:
Is there a complete, yet simple way to implement mysql_real_escape_string and whatever else is necessary, to allow a user to submit data directly to the database and have some level of assurance that your server and website will not be destroyed?

No. Your application must implement all such checks for the specific application itself - these will be different from any other.

If you want to prevent SQL injection attacks (which is what you're mainly suggesting, I think), it is sufficient to use a library which supports parameterised queries and use those (examples: mysqli, PDO, PEAR D😎

Other attacks need specific measures to prevent them. Mostly it's about escaping data where necessary, rather than taking arbitrary measures like stripping tags or removing certain characters, which might remove data you want in some cases.

If you're thinking there's some routine you can include() and then forget about any security worries, think again.

General-purpose input sanitisation routines have far too many false positives, i.e. they stop or modify legitimate input.

Mark

Roger_Ramjet

If you don't have ver 5 so you can't use PDO and are stuck with mysql_real_escape_string then use the 'best practice' function that is detailed in the manual there. Adapt and extend it for additional checks such as checking for intergers when that is the prescribed input, verifying email addies, etc.

David_P

I can test for length easy enough for input text boxes, but this doesn't seem practical for a textarea using text or longtext. Also, using text or longtext you'll have hyphens, periods, numbers, url's and such that you probably want to have, so I'm not sure what you could test for that would do any good.

I guess if someone wants to hack your site they're going to do it. Hackers are more code savvy than I am, so I'm not trying to come up with something that will stop them - I couldn't anyway. Basically, I just want something that will prevent an accidental problem by your everyday basic computer user.

Escaping the data seems to be the general cure for most accidental mishaps.