Hi, I need to disable static queries in my php code and allow only stored procedure calls. It means that there can’t be any INSERT, UPDATE, DROP, CREATE, SELECT … queries passed to database from code. Only CALL functions has to be allowed. These restrictions shouldn’t be done in code or framework level (my client demands for it). So in what level and how it should be done?
Probably model?
Is there any class or function that is dedicated for this? Like CDbCriteria or CActiveDataProvider.
How to execute a stored procedure and then ask the model to interpret the results and convert it to a model object?
class TableDataProvider extends СDataProvider {
protected $_rowCount = -1;
public $id;
public function __construct($config = array()) {
foreach ($config as $key => $value)
$this->$key = $value;
}
protected function execSP($from_row=1, $to_row=1) {
$row_count = -1;
$db = Yii::app()->db;
// sample for MSSQL!!!
// if defined id - one row, else - all
$command = $db->createCommand("exec data_get :id, :from_row, :to_row, :row_count");
$command->bindValue(':id', $this->id, PDO::PARAM_INT);
$command->bindValue(':from_row', $from_row , PDO::PARAM_INT);
$command->bindValue(':to_row', $to_row, PDO::PARAM_INT);
$command->bindParam(':row_count', $row_count, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 1);
$data = $command->queryAll();
$this->_rowCount = $row_count;
return $data;
}
protected function fetchData() {
$row_count = -1;
$from_row = 0;
$to_row = 0;
if (($pagination = $this->getPagination())!==false) {
$pagination->setItemCount($this->getTotalItemCount());
$from_row = $pagination->getOffset();
$to_row = $from_row + $pagination->getLimit();
}
else $to_row=$this->getTotalItemCount()+1;
$tmpData=$this->execSP($from_row+1,$to_row);
return $tmpData;
}
protected function fetchKeys() {
$keys = array();
foreach ($this->getData() as $i => $data)
$keys[$i] = $data[$this->keyField];
return $keys;
}
protected function calculateTotalItemCount() {
if ($this->_rowCount < 0) {
$this->execSP();
}
return $this->_rowCount;
}
}
class Table extends CModel {
public $attr1;
public $attr2;
public $id;
private $_id;
private $_err = -1;
private $_errMess = '';
// validation rules
public function rules() {
}
public function attributeLabels() {
}
public function errMess() {
return $this->_errMess;
}
public function getData($id) {
$id = (int) $id;
$dataProvider = new TableDataProvider(array(
'id' => $id,
));
$result=$dataProvider->getData();
// .. populate model
if (isset($result[0])) {
$this->id = $id;
$this->attr1 = $result[0]['attr1'];
$this->attr2 = $result[0]['attr2'];
}
}
public function create() {
$db = Yii::app()->db;
$command = $db->createCommand('...');
//..
$command->execute();
return !$this->_err;
}
public function delete{
$db = Yii::app()->db;
$command = $db->createCommand('...');
//..
$command->execute();
return !$this->_err;
}
public function change() {
$db = Yii::app()->db;
$command = $db->createCommand('...');
//..
$command->execute();
return !$this->_err;
}
}
class TableController extends CController {
public function actionIndex() {
$dataProvider = new TableDataProvider();
$this->render('index', array('dataProvider' => $dataProvider));
}
public function actionView($id) {
$model = new Table();
$model->getData($id);//populate model
$this->render('index', array('model' => $model ));
}
public function actionEdit($id) {
$model = new Table();
if (isset($_POST['Table'])) {
$model->attributes= $_POST['Table'];
if ($model->validate() AND $model->change($id)) {
Yii::app()->user->setFlash('notice', 'Success');
} else {
Yii::app()->user->setFlash('error', $model->errMess());
}
}
$model->getData($id);
$this->render('edit', array('model' => $model));
}
}
