Protection from injection - mysql_real_escape_string

[makenoiz]

ZC 1.5.1


Im adding some data to a new table I created within zencart. Im tring to become better at protecting against injections and just need a quick look over ( Im still newish and solid protection ) .... in the following is zen_db_prepare_input enough protection or do I need to add mysql_real_escape_string like this?

zen_db_prepare_input(mysql_real_escape_string($buildacmeProductID))

$sql_prods = 'INSERT INTO acme_products(`acme_products_id`,`acme_products_partno`, `acme_products_orderno`, `acme_manus_id`, `acme_model_id`, `acme_products_years`, `acme_products_type`, `acme_products_catid`, `acme_products_catname`, `acme_products_matid`, `acme_products_matname`) VALUES ';
			$sql_prods .= sprintf( "\n('%s','%s', '%s', %d, %d, '%s', %d, %d, '%s', %d, '%s'),",
			zen_db_prepare_input($buildacmeProductID),
			zen_db_prepare_input($resultProduct->attributes()->partNo),
			zen_db_prepare_input($resultProduct->attributes()->orderNo),
			zen_db_prepare_input($resultProduct->Manufacturer->attributes()->id),
			zen_db_prepare_input($resultProduct->Model->attributes()->id),
			zen_db_prepare_input($resultProduct->Years),
			zen_db_prepare_input($resultProduct->ProductType->attributes()->id),
			zen_db_prepare_input($resultProduct->ProductType->attributes()->categoryID),
			zen_db_prepare_input($resultProduct->ProductType),
			zen_db_prepare_input($resultProduct->Material->attributes()->id),
			zen_db_prepare_input($resultProduct->Material));
					
			$sql_prods = rtrim($sql_prods, ',') . ';';

			if(!mysql_query($sql_prods)){
			  echo '<h1 style="color: red;">Error</h1><p>', mysql_error(), '<p>';
			}else{
				$counter_prods++;
				echo "<BR />Successfully entered this product. ".$counter_prods." Product Records have been inserted into the database so far.";
			}

[DrByte]

This is the recommended way:

    $sql = "INSERT INTO " . TABLE_REVIEWS . " (products_id, customers_id, customers_name, reviews_rating, date_added, status)
            VALUES (:productsID, :customersID, :customersName, :rating, now(), " . $review_status . ")";

    $sql = $db->bindVars($sql, ':productsID', $_GET['products_id'], 'integer');
    $sql = $db->bindVars($sql, ':customersID', $_SESSION['customer_id'], 'integer');
    $sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' ' . $customer->fields['customers_lastname'], 'string');
    $sql = $db->bindVars($sql, ':rating', $rating, 'string');

    $db->Execute($sql);

    $insert_id = $db->Insert_ID();

The :productsID (and the other placeholders starting with a colon) are put into the $sql query, and then the bindVars() call is run to replace :productsID with the value from $_GET['products_id'] after first sanitizing it into integer format. Same for :customersID, which gets replaced with the value of $_SESSION['customer_id'] after ensuring it is an integer value.

Similar is done for :customersName which gets replaced with firstname-plus-space-plus-lastname after sanitizing those values to be safe strings.
And same with :rating which is replaced with $rating after first sanitizing it as a string.
Finally the $sql is executed by the $db->Execute($sql) call.
If you need the ID of the record inserted, you can get it as shown. Or just ignore it if you don't need it for output or other use immediately.

[makenoiz]

Thank you very much.

[makenoiz]

Hi Dr Byte,
my data is coming from a data feed using simpleXMLElement. My data is coming in fine as I am echoing it and I can see its ok but my function is halting when it hits the $sql statements. I never see my echo Debug 10 statement.

My page is a custom page within Zen Cart Admin directly under shop/admin/ . I have require application_top and set global $db in my function

$sql = "INSERT INTO " . TABLE_ACME_PRODS . " (`acme_products_id`,`acme_products_partno`, `acme_products_orderno`, `acme_manus_id`, `acme_model_id`, `acme_products_years`, `acme_products_type`, `acme_products_catid`, `acme_products_catname`, `acme_products_matid`, `acme_products_matname`, `acme_products_dateAdded`)
            VALUES (:products_id, :products_partno, :products_orderno, :products_manuid, :products_modelid, :products_years, :products_typeid, :products_catid, :products_typename, :products_matid, :products_matname, :products_dateAdded)";
                $sql = $db->bindVars($sql, ':products_id', $buildacmeProductID, 'string');
                $sql = $db->bindVars($sql, ':products_partno',$resultProduct->attributes()->partNo, 'string');
                $sql = $db->bindVars($sql, ':products_orderno',$resultProduct->attributes()->orderNo, 'string');
                $sql = $db->bindVars($sql, ':products_manuid',$resultProduct->Manufacturer->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_modelid',$resultProduct->Model->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_years',$resultProduct->Years, 'string');
                $sql = $db->bindVars($sql, ':products_typeid',$resultProduct->ProductType->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_catid',$resultProduct->ProductType->attributes()->categoryID, 'string');
                $sql = $db->bindVars($sql, ':products_typename',$resultProduct->ProductType, 'string');
                $sql = $db->bindVars($sql, ':products_matid',$resultProduct->Material->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_matname',$$resultProduct->Material, 'string');
                $sql = $db->bindVars($sql, ':products_dateAdded',now(), 'date');
            
                 echo "<BR />DEBUG 10 SQL statement = ".$sql;
                
                $db->Execute($sql);

Do you see anything out of place or is there anything else I need to require or globalize in my fxn?

Thanks!

[makenoiz]

Just tried with using the example in the developers documentation located at: http://www.zen-cart.com/wiki/index.p...Vars_Datatypes

and it worked so it must be my code somewhere....... I can use my own custom variables in the 3 option of the BindVars function right?

Ill look into it further......

[makenoiz]

This is holding it up.... any idea why?

$sql = $db->bindVars($sql, ':products_dateAdded',now(), 'date');

Can I use now() in the statement?

[RodG]

Do you see anything out of place

I do. :)

$sql = "INSERT INTO " . TABLE_ACME_PRODS . " (`acme_products_id`,`acme_products_partno`, `acme_products_orderno`, `acme_manus_id`, `acme_model_id`, `acme_products_years`, `acme_products_type`, `acme_products_catid`, `acme_products_catname`, `acme_products_matid`, `acme_products_matname`, `acme_products_dateAdded`)
            VALUES (:products_id, :products_partno, :products_orderno, :products_manuid, :products_modelid, :products_years, :products_typeid, :products_catid, :products_typename, :products_matid, :products_matname, :products_dateAdded)";
                $sql = $db->bindVars($sql, ':products_id', $buildacmeProductID, 'string');
                $sql = $db->bindVars($sql, ':products_partno',$resultProduct->attributes()->partNo, 'string');
                $sql = $db->bindVars($sql, ':products_orderno',$resultProduct->attributes()->orderNo, 'string');
                $sql = $db->bindVars($sql, ':products_manuid',$resultProduct->Manufacturer->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_modelid',$resultProduct->Model->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_years',$resultProduct->Years, 'string');
                $sql = $db->bindVars($sql, ':products_typeid',$resultProduct->ProductType->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_catid',$resultProduct->ProductType->attributes()->categoryID, 'string');
                $sql = $db->bindVars($sql, ':products_typename',$resultProduct->ProductType, 'string');
                $sql = $db->bindVars($sql, ':products_matid',$resultProduct->Material->attributes()->id, 'string');
                $sql = $db->bindVars($sql, ':products_matname',$$resultProduct->Material, 'string');
                $sql = $db->bindVars($sql, ':products_dateAdded',now(), 'date');
            
                 echo "<BR />DEBUG 10 SQL statement = ".$sql;
                
                $db->Execute($sql);

See highlighted double $$

Cheers
Rod

[gilby]

oops disregard...

[makenoiz]

Thanks! Ill fix and get back with result.....

[DrByte]

You can use now() but you must place it inside quotes: 'now()'