[SOLVED] serialize and qoutes?

[fivestringsurf]

Hello,

 

I was wondering, is it bad to store a serialized output in a mysql field  the way it is generated including quotes that are not escaped with a backslash?

for example:  a:3:{i:0;s:4:"bass";i:1;s:6:"salmon";i:2;s:4:"nemo";}

Do the unescaped quotes pose a security threat? they are created from checkbox defined values and not user-typed input?

 

I am pulling data from a check box form and storing it in one field.  Originally I was creating my own array and separating the values with a comma, but the serialize function seems like a nifty shortcut for what i was trying to do anyway.

 

[jonsjava]

it would be easy for someone to change the checkbox values to turn it into an injection. Keep that in mind. NEVER trust foreign input.

[fivestringsurf]

that was kinda my feeling...so

how on earth do i escape the quotes generated by serialize(); ?

$fish = serialize($_POST['fish']); //check boxes
$fish = mysql_real_escape_string($fish);

..doesn't work.  i read here that i need to escape first then serialize then insert into database...?

 

How do i escape the string when the quotes that need escaping are created by serialize() itself???

[p2grace]

Try just reversing the order of the functions:

 

$fish = serialize(mysql_real_escape_string($_POST['fish'])); //check boxes

[fivestringsurf]

tried it... it throws this error

$fish = serialize(mysql_real_escape_string($_POST['fish'])); //check boxes

Warning: mysql_real_escape_string() expects parameter 1 to be string, array

 

it's an array of check boxes so that's like expecting echo($somearray) to work...ya know. ?

 

The weirdest part is it echos out escaped but when you check out what actually gets written to the database the quotes are not escaped... like

$fish= serialize($_POST[fish]); //check boxes
$fish= addslashes($fish);
>insert value into database...>
echo $fish;

 

I think it has to do with the numerical values asigned to the serialized string...which I read in a post around here, but if that's the case what's an easy way to get the serialized string escaped?

[p2grace]

Try this:

 

function cleandata($arr){
// Clean data in preparation for db entry
$cleandata = array();
foreach($arr as $key => $item){
	$clean = trim(mysql_real_escape_string($item));
	$cleandata [$key] = $clean;
}
return $cleandata;
}

$fish = serialize(cleandata($_POST['fish']));

[fivestringsurf]

no good, but i do really appretiate all your efforts ! 

I actually was using a similar function as i do with most things prior to inserting into db...i just wrote addslashes here to keep it simple.

 

It's so weird how it echos escaped and inserts un-escaped?  I can't be the only one battling this strange behavior.  It would think it makes good speedy sense to put a group of check boxes into one mysql field instead of writing 20 extra columns.

[corbin]

It would think it makes good speedy sense to put a group of check boxes into one mysql field instead of writing 20 extra columns.

 

 

From a design standpoint, yes, they should be separated.  Let's say you want to find all users that have checked the 3rd check box...  Using just MySQL to do that would be beastly.

 

 

But, if you still want to serialize it, you would do it like:

 

 

$data = mysql_real_escape_string(serialize($some_string));

 

 

 

If you're wondering about the ", they are not escaped.  ' is escaped along with some other stuff.  " poses no threat to MySQL statements, assuming you're using ' in your statements.

[fivestringsurf]

$fish= cleanForEntry(serialize($_POST['fish']));

 

is what i used from the start...but because i didn't actually see the double quotes being escaped I got nervous, but I guess it's safe.

 

here's my function i wrote for inserting data:

function cleanForEntry($input) { 
$input = trim($input); 
$input = strip_tags($input);
$input = eregi_replace("`", "", $input);
	if (!get_magic_quotes_runtime()) {
  	 	$input = mysql_real_escape_string($input);
	}
    return $input; 
} 

 

I know what your saying about the sorting db stuff corbin...I'm just always trying to keep my tables from becoming 100 fields deep.(not sure exactly what's fastest)  Even if i stored all my check box values in a single field couldn't I search the field for a value I'm looking for?

 

[Daniel0]

Even if i stored all my check box values in a single field couldn't I search the field for a value I'm looking for?

 

I'd like to see you do that

[corbin]

"Even if i stored all my check box values in a single field couldn't I search the field for a value I'm looking for?"

 

 

Yes, but not nearly as quickly/easily.

 

Edit: Daniel beat me to answering that .

 

 

 

As for your cleanForEntry function, it's kind of overkill.

 

 

Instead of running strip_tags, you could just do htmlentities($var) when ever outputting data.

 

And the eregi_replace is wrong.  You should use str_replace instead since it's not using a pattern, or you should just not use it.  ` isn't harmful unless it's in the placement of an object name.  If you ever have user input signifying a table name for example you might have to worry about ` (but in that case, you would want to whitelist, not blacklist.)

 

And:

 

if (!get_magic_quotes_runtime()) {

  $input = mysql_real_escape_string($input);

}

 

 

If magic_quotes are enabled, the data should be stripslashes()'d then run through mysql_real_escape_string.

 

 

mysql_real_escape_string is a function in the MySQL driver (well, it references a function in the MySQL driver).  That doesn't automatically make it safer, but that means it's specifically made to be used with MySQL (even though addslashes was too.)  Basically what I'm getting at is mysql_real_escape_string escapes more than addslashes() does.  Also, m_r_e_s is aware of character encoding and what not.

[PFMaBiSmAd]

get_magic_quotes_runtime() has nothing to do with external GET/POST/COOKIE data. You should be using get_magic_quotes_gpc()

[fivestringsurf]

Damn!  I got schooled today!

 

I love it.

 

Thank's guys 

[PFMaBiSmAd]

i didn't actually see the double quotes being escaped

 

The double-quotes are escaped in the query string (if your code is doing what you expect). The escape characters don't appear in the actual database table if the data is escaped correctly at the time it is put into the table.

[fivestringsurf]

i didn't actually see the double quotes being escaped

 

The double-quotes are escaped in the query string (if your code is doing what you expect). The escape characters don't appear in the actual database table if the data is escaped correctly at the time it is put into the table.

 

 

That puts spins my head right around, only because whenever I look  data in the database through phpmyadmin  "quotes" look like \"quotes\"  and 'quotes' look like \'quotes\'.

 

And corbin...no doubt much of what write is bloated, I'm fairly new at this...good advice!  I was using the strip_tags function only because I thought that was to prevent cross site scripting hacks through <script> and stuff?

[Daniel0]

That puts spins my head right around, only because whenever I look  data in the database through phpmyadmin  "quotes" look like \"quotes\"  and 'quotes' look like \'quotes\'.

 

Then you are double escaping it. Look through your code and see if you are running it through an escaping function twice.

 

And corbin...no doubt much of what write is bloated, I'm fairly new at this...good advice!  I was using the strip_tags function only because I thought that was to prevent cross site scripting hacks through <script> and stuff?

 

Your database is not vulnerable to XSS. There are different contexts in which data might appear. In an SQL context you would escape it for SQL. In an HTML context you would escape it in another way (and yet another way in e.g. JSON).

[fivestringsurf]

it's probably that dang magic quotes sprinkling backslashes all over the place!

I had escaping all wrong...how do you tell if something is escaped (visually) if you

can't see the backslashes?

[p2grace]

Type in something with quotes, if it gets into the database then you know it worked

[Daniel0]

Are you talking about when the data is in the database? It has no effect there. It's only so characters that have special meaning in an SQL query will not mess up the query when you use them.

 

Say I want to insert John in a table:

INSERT INTO table (something) VALUES ('John');

 

That works well, but if I want John's we'll get in trouble:

INSERT INTO table(something) VALUES ('John's');

 

In this case, the single quote has a special meaning in the sense that it's a string delimiter. Thus it has to be escaped.

[fivestringsurf]

you guys rock.

You actually taught me something today.  I had escaping all wrong.

I mean sooo wrong!  Wow this thread went around the world and back again.

I think I'll stick around much learning to be had.