Jump to content



Photo
- - - - -

pdo_exception or not


  • Please log in to reply
3 replies to this topic

#1   foxp2

foxp2

    strong as a Twig

  • Banned
  • 310 posts
  • Real Name:Laurent
  • Gender:Male
  • Location:France

Posted 29 March 2012 - 12:50

Hi,
in my scenario, i have 3 Site : Shop / Admin / PublicAdmin.
Each site shares the same database.
because my PublicAdmin is a live sample, i've protected some datas.
each table had received a new row 'protected' (0 by default, 1 for protected data)

In my PublicAdmin, i've changed the sql query.
example for Customers Application :
.
\osCommerce\OM\Core\Site\PublicAdmin\Application\Customers\SQL\ANSI\Delete.php : (class deleted)
<?php

  class Delete {
	public static function execute($data) {
	  $OSCOM_PDO = Registry::get('PDO');
	  $Qdelete = $OSCOM_PDO->prepare('delete from :table_customers where customers_id = :customers_id');
	  $Qdelete->bindInt(':customers_id', $data['id']);
	  $Qdelete->execute();
	  return ( $Qdelete->rowCount() === 1 );
	}
  }
?>

became :
\osCommerce\OM\Core\Site\PublicAdmin\Application\Customers\SQL\MySQL\Standard\Delete.php :
<?php

  class Delete {
	public static function execute($data) {
	  $OSCOM_PDO = Registry::get('PDO');
	  $Qdelete = $OSCOM_PDO->prepare('CALL DeleteCustomer(:customers_id)');
	  $Qdelete->bindInt(':customers_id', $data['id']);
	  $Qdelete->execute();	
	  return ( $Qdelete->rowCount() === 1 );
	}
  }
?>

the stored procedure inserted in database :

DROP PROCEDURE `DeleteCustomer`;
DELIMITER ;;
CREATE PROCEDURE `DeleteCustomer` (IN `custid` int)
BEGIN
DECLARE msg VARCHAR(255);
IF (SELECT protected FROM osc_customers WHERE customers_id = custid AND protected = 1) THEN
set msg = CONCAT("This action is forbidden for the customer with the ID " , custid);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
ELSE
DELETE FROM osc_customers WHERE customers_id = custid;
END IF;
END;;
DELIMITER ;

For now, i catch the message (returning by the stored proc) with this code in :

\osCommerce\OM\Core\PDOStatement.php :

..../...	  
if ( $this->_is_error === true ) {		
		
		   Registry::get('MessageStack')->add(null, self::_getErrorMessage() , 'error');
		  
		  }  
.../...


	private function _getErrorMessage() {
  
	  $error_array = array();
	
	  $error_array = $this->errorInfo();
	
	  return end($error_array);	
	}

i've excluded error no & sqlstate

so, if a user in PublicAdmin deletes a protected customer, MessageStack displays :

This action is forbidden for the customer with the ID x
Error: There was a problem performing the action.


my code works.
but i wonder if it is better to introduce a new exception class for pdo, and if yes, how to integrate with the existant errorlog module ?
i work with mysql 5.5

developpers, what do you think about it ?

Regards

Edited by foxp2, 29 March 2012 - 12:56.

-------------------

#2   Gergely

Gergely

    Action Hero

  • Community Team
  • 1,123 posts
  • Real Name:Gergely Tóth
  • Gender:Male
  • Location:Budapest

Posted 29 March 2012 - 13:44

@ foxp2

Hi Laurent,

I think would be better exeption class because in some known host not allowed procedures for security risks so we have to ask the service to install something...
At this moment I dont have any idea how can generate "sql like errors" but this is would not be incredible even if do other error handlers.

Regards,
Gergely
some rewrites :-)

#3   foxp2

foxp2

    strong as a Twig

  • Banned
  • 310 posts
  • Real Name:Laurent
  • Gender:Male
  • Location:France

Posted 29 March 2012 - 15:58

@Gergely


some known host not allowed procedures for security risks

But they don't really matter to me as far as I'm concerned

Today, I'm developping in the Cloud (with a private git repositery), with Pagodabox.
Like many others major script (Magento, Dupral, WP for example) i'm optimizing osCommerce 3 with this Architecture. (github account : https://github.com/pagodabox )
And that's why, for example, Queries are deported in Store Procedures.

Proof of concept :
Shop (http://oscommerce3.p...x.com/index.php)
Admin (http://oscommerce3.p...php?Admin)
and PublicAdmin (http://oscommerce3.p...blicAdmin) run in one instance, but with Pagodabox, i can separate each site in a different instance.

My work is not only to add a framework like Kendo.
-------------------

#4   foxp2

foxp2

    strong as a Twig

  • Banned
  • 310 posts
  • Real Name:Laurent
  • Gender:Male
  • Location:France

Posted 30 March 2012 - 07:07

Resolved.
before, we had :

PHP Warning: PDOStatement::execute(): SQLSTATE[45000]: <<Unknown error>>: 1644 This action is forbidden for the customer with the ID X in \osCommerce\OM\Core\PDOStatement.php on line 66


I've changed all my SQLSTATE and filtering it in errorlog's module.
Now, if one of my store procedure returns a certain SQLSTATE[xxxxx] type , there is no record in error_log, just a messagestack in header via _getErrorMessage() protected method.
In addition,since mysql 5.5, Trigger (like SP) can return an error via a SIGNAL. Very useful in this case.
-------------------