[SOLVED] mysql_real_escape_string - where to use?

[damianjames]

Hi all -

 

I'm grabbing a product category from a $_GET and want to make sure it's secure, so I figure mysql_real_escape_string is what I need to be doing.  The php manual says to put it around every variable, but then gives an example that I can't seem to directly correlate to that statement.  Here's my code:

 

<?php
					$prodgroup = $_GET['prodgroup'];
					$db = @mysql_connect("localhost", "user", "pass");
					if( ! ($db = @mysql_connect("localhost", "user", "pass")) ) {
					} else {
						mysql_select_db("dbname",$db) or die("Select DB Error: ".mysql_error());
					}
					$numcols = 3;
					$numcolsprinted = 0;
						$query = "SELECT item,name_esp,name_eng,prodgroup,ret_price,dimension FROM `products` WHERE prodgroup = \"$prodgroup\" AND active = \"true\" ORDER BY name_$lang";
						$mysql_result = mysql_query($query, $db);
					while($myrow = mysql_fetch_row($mysql_result))
					{
						$item = $myrow[0];
						$name_esp = $myrow[1];
						$name_eng = $myrow[2];
						$prodgroup = $myrow[3];
						$ret_price = $myrow[4];
						$dimension = $myrow[5];
						if ($numcolsprinted == $numcols) {
							print "</tr>\n<tr>\n";
							$numcolsprinted = 0;
						}
						echo ("<td class=\"product_cell\" valign=\"top\"><a href=\"productdetail.php?lang=$lang&item=$item\"><strong>$prod_label[0]</strong> $item<br />");
						if ($lang == "esp") {
							echo("<strong>$prod_label[1]</strong> $name_esp<br /><img src=\"/images/products/thumbs/p$item.jpg\" /><br /><strong>$prod_label[4]</strong> $dimension<br /><strong>$prod_label[3]</strong> $ret_price</a></td>\n");
						}
						else {
							echo("<strong>$prod_label[1]</strong> $name_eng<br /><img src=\"/images/products/thumbs/p$item.jpg\" /><br /><strong>$prod_label[4]</strong> $dimension<br /><strong>$prod_label[3]</strong> $ret_price</a></td>\n");
						}
						$numcolsprinted++;
					} 
					$colstobalance = $numcols - $numcolsprinted;
					for ($i=1; $i<=$colstobalance; $i++) {
					}
				?>

 

Is the best place to put mysql_real_escape_string here?

 

$prodgroup = mysql_real_escape_string($_GET['prodgroup']);

 

Or here?

 

$item = mysql_real_escape_string($myrow[0]);
$name_esp = mysql_real_escape_string($myrow[1]);
$name_eng = mysql_real_escape_string($myrow[2]);
$prodgroup = mysql_real_escape_string($myrow[3]);
$ret_price = mysql_real_escape_string($myrow[4]);
$dimension = mysql_real_escape_string($myrow[5]);

 

Or somewhere else?

 

I don't quite understand the example at php.net:

 

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));
?>

 

Because I don't see where that variable is being initialized.

 

Thanks all for your help!

[premiso]

You always want to escape data coming in from POST or GET data to make sure SQL Injection won't be an issue (even if it is not suppose to be text in a db).

 

Also escaping the data coming out of the database is only necessary for varchar or text, as this data could contain a ' which will break the SQL.

 

Hope that helps.

 

Edit:

I seemed to have missed a key, only escape data coming out of the DB, if you are going to be putting it back in. Do not escape it if you plan to display it on a page =) Thanks to wildteen for pointing that out.

[wildteen88]

mysql_real_escape_string should only be used when inserting data into your database. you dont use mysql_real_escape_string for data coming out of the database.

[damianjames]

mysql_real_escape_string should only be used when inserting data into your database. you dont use mysql_real_escape_string for data coming out of the database.

 

In that case, do I need to worry about stripslashes or mysql_real_escape_string with what I am doing?  premiso states above that you should escape the post or get data.

 

Thanks again!

[Mchl]

mysql_real_escape_string should only be used when inserting data into your database. you dont use mysql_real_escape_string for data coming out of the database.

 

Maybe I' nitpicking, but I believe you meant: "whenever a query is send to database". After all SQL injection is perfectly possible with SELECT statement.

[premiso]

mysql_real_escape_string should only be used when inserting data into your database. you dont use mysql_real_escape_string for data coming out of the database.

 

In that case, do I need to worry about stripslashes or mysql_real_escape_string with what I am doing?  premiso states above that you should escape the post or get data.

 

Thanks again!

 

You escape data going into a Database. Any data potentially used for pulling out a query that is coming from an outside source, such as GET or POST, you escape it to avoid SQL Injection.

[damianjames]

Ok, so I'll escape the $_GET.  Thanks again for all your help, much appreciated!