...

View Full Version : mysql_real_escape_string quick question



nikos101
01-10-2008, 04:25 PM
howdy coders


$id = mysql_real_escape_string($_POST['id']);
$news_title = mysql_real_escape_string($_POST['news_title']) ;
$date = mysql_real_escape_string($_POST['date']);
$description = mysql_real_escape_string($_POST['description']) ;

$update = "UPDATE nikos_news SET Title = '$news_title', NewsDate = '$date', DescriptionDoc = '$description' WHERE NewsItemID='$id' ";
is the same as


$id = $_POST['id'];
$news_title = $_POST['news_title'] ;
$date = $_POST['date'];
$description = $_POST['description'] ;

$update =mysql_real_escape_string( "UPDATE nikos_news SET Title = '$news_title', NewsDate = '$date', DescriptionDoc = '$description' WHERE NewsItemID='$id' ");right?

Inigoesdr
01-10-2008, 04:45 PM
No, the second one will escape the quotes of the query too, which will cause an SQL error: SET Title = ' <--

nikos101
01-10-2008, 04:49 PM
ah ok, cool. I suppose I'll also want to add strip_tags() for extra security

$description = strip_tags(mysql_real_escape_string($_POST['id']));

felgall
01-10-2008, 08:28 PM
You should really validate the field content for security. What validation a field requires depends on what the field is supposed to contain. The more specific you can make the validation the less likely injection of invalid code will be.

Escaping content is used to allow characters in the content to be distinguished from the same character delimiting content and has no real bearing on the security of fields.

kbluhm
01-10-2008, 11:08 PM
ah ok, cool. I suppose I'll also want to add strip_tags() for extra security

$description = strip_tags(mysql_real_escape_string($_POST['id']));

I've seen this a lot over the past week. strips_tags() is not a be-all end-all "extra" security solution.

I suppose you'll also want to add strip_tags() if you don't want HTML tags to be saved in the database.

If you want HTML tags to be saved and displayed as code, use htmlentities() or htmlspecialchars().

But strip_tags() should not be linked to the thought "oh, I want to securify this... there, all set".

nikos101
01-11-2008, 09:38 AM
so $description = htmlentities(mysql_real_escape_string($_POST['id']));is better

abduraooft
01-11-2008, 01:24 PM
so $description = htmlentities(mysql_real_escape_string($_POST['id']));is better

foreach ($_POST as $key => $value)
{
$$key=htmlentities(mysql_real_escape_string(($value));
}
echo $id;
echo $description;
:thumbsup:

kbluhm
01-11-2008, 05:24 PM
No. No, no, no, no, no. Neither of you understand.

If you want to save HTML code, do not run it through htmlentities() as it will increase the size of the data being saved. &gt; is 3 characters larger than >.

If you are accepting an integer, check if with ctype_digit(), then cast as an integer if needed.
If you want to save and display everything as is, just save it.
If you are removing HTML, send it though strip_tags() as then save it.
If you are displaying HTML as code, save it as is and display it through htmlentities().
If you are saving a string, run it through mysql_real_escape_string().
Etc...
Etc...
Etc...

Always run strings that are non-numeric through mysql_real_escape_string().

You do not need to run a digit through htmlentities(), mysql_real_escape_string(), strip_tags(). There are no HTML tags or escapable characters in a digit. It is a digit.

Write your form processor as though you have no idea where it is coming from, and every form field is an input, multi-select, checkbox, radio button, and textarea... combined.

If you want to save a digit and a string, do the following:


// this should be a digit
$digit = ( isset( $_POST['digit'] ) AND ctype_digit( $_POST['field'] ) ) ? $_POST['field'] : 0;
// this should be a string
$string = ( isset( $_POST['string'] ) ) ? mysql_real_escape_string( $_POST['string'] ) : '';


Now realize these are just examples.

If you want to receive an email address, zip code, phone number, etc, use a regular expression. It's all common sense really. The previous foreach() solution is just lazy.

aedrin
01-11-2008, 05:47 PM
Escaping content is used to allow characters in the content to be distinguished from the same character delimiting content and has no real bearing on the security of fields.

False, it's one of the primary concerns with security. This prevents SQL Injection which is your main concern when accepting user input for storing in a database.


What abduraooft suggested, is not just plain lazy, it's also dangerous as it is easy to break your website. And it might be hard to fix a bug if it is caused by this.


If you want to save HTML code, do not run it through htmlentities() as it will increase the size of the data being saved. &gt; is 3 characters larger than >.

I don't think length is really a security concern. It's the reason for storage that matters (as you said)




so $description = htmlentities(mysql_real_escape_string($_POST['id']));
is better

Actually, it would be even better if we stopped copy and pasting code for multiple fields. (requiring modifications to each line whenever we change the validations)



function escapeHTML($input) {
return htmlentities(mysql_real_escape_string($input));
}

$description = escapeHTML($_POST['description']);


Remember also that one can use prepared statements instead of mysql_real_escape_string(). It makes for cleaner code, and in some ways safer.

kbluhm
01-11-2008, 05:54 PM
I don't think length is really a security concern. It's the reason for storage that matters (as you said)
Agreed, that was more of a side point dealing with common-sense storage.

sprintf() can be your friend as well when it comes to properly formatting queries.

aedrin
01-11-2008, 05:58 PM
I've never considered using sprintf() for writing queries, though I suppose that would work well.

But I always use prepared statements so I've never needed to do it like that.

I've made it transparent enough to where it doesn't require extra code either.



$result = query("SELECT * FROM table WHERE id = ?", array($id));


I just wish that PHP had a shortcut for creating arrays like other languages do... to be able to do something like this:


$array = {$id1, $id2, $id3};

Brandoe85
01-11-2008, 07:23 PM
Are people still really using inline sql like this? I can't imagine writing any application code without the use of stored procedures, views etc. I can't recall which version of mysql you'll need for support but I can assure you it will make your life easier for coding and for maintenance.

felgall
01-12-2008, 02:18 AM
False, it's one of the primary concerns with security. This prevents SQL Injection which is your main concern when accepting user input for storing in a database.

Utter garbage. - there is no one code to add to any PHP to provide security. Using that call does NOT prevent injection, it escapes the quotes in the entered content so that they are treated correctly as content instead of being mistaken for delimiters. Unless those characters are ALLOWED to be in that field in the first place then running the field through that call is completely unnecessary as the content will have been rejected as invalid by your proper validation long before getting to that call.

If a field must be an integer then test that it is an integer, don't escape any quotes in the content because that field is not allowed to have quotes in it at all. The same applies to fields that must be all alphabetic, alphanumeric, etc. You only need to use mysql_real_escape_string() when the field is legitimately allowed to contain a character that needs to be escaped.

Why do so many people consider certain functions too be the magic security cure for their site. Sure using mysql_real_escape_string() will prevent SQL injection but unless you are saving SQL into the field then you end up storing crap in your database if that is all you use. Validate your data properly and then you don't end up with a database full of meaningless rubbish.

htmlentities() is a function to use for displaying content on a web page and should ONLY be used when outputting to the page - using it before you store data in the database just makes the database bigger and gives no benefit whatsoever.

Using mysql_real_escape_string() by itself to secure your site is as effective as pulling out your magic want and yelling "abracadabra". Both are magical solutions that require magic to really work in order for them to be effective and neither one works in the real world.

If someone does think that mysql_real_escape_string() is somehow a real way of preventing SQL injection then can they please post some code that demonstrates how to do SQL injection in a person's name field, a phone number field, an email address, a street address, and other similar fields as I would be interested in seeing how such an injection into one of those field types could be done that requires the use of mysql_real_escape_string() to block it.

aedrin
01-14-2008, 04:36 PM
Using that call does NOT prevent injection

Quote from php.net/mysql_real_escape_string:

Using mysql_real_escape_string() around each variable prevents SQL Injection.



Why do so many people consider certain functions too be the magic security cure for their site.

You are mixing statements. No one said that 1 statement is the magic cure. We are talking about SQL injection with simple inputs in to a query.

Mysql_real_escape_string() or prepared statements both protect against SQL injection.


If someone does think that mysql_real_escape_string() is somehow a real way of preventing SQL injection then can they please post some code that demonstrates how to do SQL injection in a person's name field, a phone number field, an email address, a street address, and other similar fields as I would be interested in seeing how such an injection into one of those field types could be done that requires the use of mysql_real_escape_string() to block it.

You want to know how SQL injection works? Look at example #3 at http://us2.php.net/mysql_real_escape_string.

felgall
01-14-2008, 07:17 PM
Most properly validated fields wouldn't be vulnerable to SQL injection in the first place since few fields can validly contain anything that contains SQL. If you validate your fields properly then very few injection attempts if any should get that far. If none of your fields can validly contain SQL then using that code doesn't do anything to prevent injection because only valid content would ever get to that statement. Then all it does is escapes any valid quotes that are in the content which has nothing to do wit SQL injection.

You should ALWAYS validate your fields PROPERLY rather than rely on code liike that to prevent injection as relying on that command also fills your database with garbage.

kbluhm
01-14-2008, 07:22 PM
You should ALWAYS validate your fields PROPERLY rather than rely on code liike that to prevent injection as relying on that command also fills your database with garbage.

So then we've gotten this far only to come full-circle to my original point. ;)

aedrin
01-14-2008, 07:44 PM
Most properly validated fields wouldn't be vulnerable to SQL injection in the first place since few fields can validly contain anything that contains SQL.

In either case, you're better off using that function or prepared procedures, which preserves input and prevents SQL injection.

I don't believe in being too restrictive. In my opinion, validation should only be applied on critical fields (dates, email addresses, etc.)

hammer65
01-14-2008, 07:47 PM
Most properly validated fields wouldn't be vulnerable to SQL injection in the first place since few fields can validly contain anything that contains SQL. If you validate your fields properly then very few injection attempts if any should get that far. If none of your fields can validly contain SQL then using that code doesn't do anything to prevent injection because only valid content would ever get to that statement. Then all it does is escapes any valid quotes that are in the content which has nothing to do wit SQL injection.

You should ALWAYS validate your fields PROPERLY rather than rely on code liike that to prevent injection as relying on that command also fills your database with garbage.

Nobody is disputing that proper validation is important. However, often you will have a free form text field where there is no clear way to "validate" the content. In that case it is a waste of code to look for SQL keywords or something similar when you can simply escape it. Yes there is a possibility that you will end up with garbage in your database (use a cron to look for them later and eliminate those records), but compared to having your server owned, that's nothing.

I know you are familiar with Chis Shifflet's blog postings on the subject. I took the advice given so far in this thread (with some exceptions) as accurate enough and not really disparaging the need to validate.

I do agree that you should not pollute your dataset with document specific encoding. It not only doesn't gain you anything in terms of security and creates bloat, but it also makes the data less versatile in porting to other formats such as PDF, CSV. It can also complicate searching and other queries that work with the data.

Some hosts still don't support newer MySQL extensions, even if they have MySQL 5.x installed. The old extension is such a staple in PHP, that even if other solutions are supported in the distro, I suspect they still don't get used much. It would help if tutorial and book authors started using more propared statements in their writing.

ahallicks
01-14-2008, 10:02 PM
I don't want to do php anymore :(

hammer65
01-14-2008, 10:12 PM
I don't want to do php anymore :(

It doesn't get any easier in any other language.

nikos101
02-01-2008, 05:59 PM
Well this is a great thread, I'm only just starting to begin to get head around all this security stuff, I've got a long way to go though!

Thanks for all your interesting thoughts so far!! :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum