mysql_real_escape string or htmlspecialchars, etc. what to use?

[justAnoob]

I got a little form with a text area. a user can enter a comment and then it gets inserted into the database. right now on the i'm using mysql_real_escape_string($_POST['var']) and then to display it I'm using echo $row['var']    It works, but am I in danger of my db be destroyed?  That way that I have it setup a user can type in something like

 

a'a'a b"b"b"  (10/10)

 

and it gets entered into the db just like that

then when it gets echoed, it looks just fine.

 

Is this bad. What else should I be doing to secure these comment boxes of mine.  btw, in mysql the column is set to TEXT, if that means anything. Thanks.

[PravinS]

Use this function

function quote_smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc()) 
{
	$value = stripslashes($value);
}
// Quote if not a number or a numeric string
if (!is_numeric($value)) 
{
	$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}

 

[JasonLewis]

Um, mysql_real_escape_string() and htmlspecialchars() do different jobs.

 

If you're worried about SQL Injection, then mysql_real_escape_string() is fine, to a point. It protects against most injection attacks. Also note that another function, addslashes(), also does the same job as mysql_real_escape_string().

 

If you want to read up on SQL Injection, I read through this article not that long ago. It's quite good, explains a lot about SQL Injection.

[Mchl]

Also note that another function, addslashes(), also does the same job as mysql_real_escape_string().

 

No it doesn't. It does the same job in many cases, but in general should not be used in favor of mysql_real_escape_string()

[JasonLewis]

As far as I've read about addslashes(), it does almost the same job. mysql_real_escape_string() does escape a few extra characters on top of what addslashes() does, but both can be worked around, which is shown in that link I posted.

[Mchl]

Another thing that differs mysql_real_escape_string() from addslashes() is that it takes connection encoding into consideration thus lowering chances of data corruption as well as takes care of a few more possible SQL injections.

[justAnoob]

so as long as mysql_real_escape_string is used when entered in the database, is this a fairly safe way to display the info?

<?php
include 'connection.php';
$trackid = mysql_real_escape_string($_GET['trackid']);
$_SESSION['track_id'] = $trackid;
$sql = 'SELECT imgpath FROM tracks WHERE id = ' . $trackid . ' LIMIT 1';
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{
echo '<img src="' . $row['imgpath'] . '" />';
}
mysql_close;
?>

[salathe]

so as long as mysql_real_escape_string is used when entered in the database, is this a fairly safe way to display the info?

 

It's better than nothing. Another, preferable, technique in the code that you just posted would be to explicitly look for digit-only input since it is really an ID number being used in the query.  Also, none of this has any bearing on whether the input will be safe for display, which is an entirely separate concern.

[justAnoob]

making sure it is numeric, i understand that. i can take care of that. As far as the script that posts the comment. Here is what I got. Is is a small script. I always see these huge scripts on google that just insert info into a database. How much further can this be taken to make sure it is somewhat secure? Thanks for all the info 

<?php
session_start();
include 'connection.php';

$comment = mysql_real_escape_string($_POST["comment"]);
$posted_on = mysql_real_escape_string($_POST["posted_on"]);
$username_id = mysql_real_escape_string($_SESSION['who']);
$track_id = mysql_real_escape_string($_SESSION['track_id']);

$sql = "INSERT INTO comments (comment, track_id, username_id, posted_on)VALUES('$comment','$track_id', '$username_id', '$posted_on')";

if (mysql_query($sql))
{ 
$url = $_SESSION['url'];
header("location:" . $url);
}
else
{
echo 'put error here';
}

mysql_close();
?>

[Mchl]

You can move to mysqli and use prepared statements. That's ultimate defense against SQL injections.

 

And as salathe mentioned above, this is just taking care of SQL side. Another layer of filtering should be employed when data is displayed.

[justAnoob]

my second post on this thread is showing the script which displays the info from the db.

[Mchl]

$row['imgpath'] should be an url, so you might validate if it really is using regex or filters. There shoud be no HTML tags in it, so you can pass it through strip_tags.

[justAnoob]

you blew my head off with that one. Can you explain a little in depth?

[Mchl]

Ok... $row['imgpath'] should contain a url to an image (or perhaps a path to an image). However, someone could somehow insert into your database something like

 

"><h1>ADMIN OF THIS PAGE SUCKS</h1>

 

you wouldn't like this, would you?

 

That's why you should filter your data before displaying (also before inserting into database, but double checking in this case makes sense).

 

[justAnoob]

ah, i see thank you for your help.