Protection from injection - mysql_real_escape_string
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))
Code:
$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.";
}
Re: Protection from injection - mysql_real_escape_string
This is the recommended way:
Code:
$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.
Re: Protection from injection - mysql_real_escape_string
Re: Protection from injection - mysql_real_escape_string
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
Code:
$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!
Re: Protection from injection - mysql_real_escape_string
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......
Re: Protection from injection - mysql_real_escape_string
This is holding it up.... any idea why?
Code:
$sql = $db->bindVars($sql, ':products_dateAdded',now(), 'date');
Can I use now() in the statement?
Re: Protection from injection - mysql_real_escape_string
Quote:
Originally Posted by
makenoiz
Do you see anything out of place
I do. :)
Quote:
Originally Posted by
makenoiz
Code:
$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
Re: Protection from injection - mysql_real_escape_string
Re: Protection from injection - mysql_real_escape_string
Thanks! Ill fix and get back with result.....
Re: Protection from injection - mysql_real_escape_string
You can use now() but you must place it inside quotes: 'now()'