Database Exception occured while saving data in yii2

Hi, I am using yii2 basic.
I have groupsavingdetails CRUD.
When I try to save the data it gives me foll error.

# Database Exception – [yii\db\Exception](http://www.yiiframework.com/doc-2.0/yii-db-exception.html)

## SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5
The SQL being executed was: SELECT count(*) 
FROM groupsavingdetails
where groupsavingdetails.GroupId=29
and groupsavingdetails.Year<=2017
and groupsavingdetails.Month<=

My model is as follows:

<?php

namespace app\models;


use Yii;
use app\models\Groupdetails;

/**
 * This is the model class for table "groupsavingdetails".
 *
 * @property integer $GroupSavingDetailsId
 * @property integer $EmpId
 * @property integer $GroupId
 * @property string $Year
 * @property string $Month
 * @property double $OpeningBalance
 * @property double $TotalSaving
 * @property double $LoanRecovery
 * @property double $LoanInterest
 * @property double $Fine
 * @property double $BankInterest
 * @property double $Expenses
 * @property double $LoanGiven
 * @property double $BankLoan
 * @property double $TotalValueofLoanGiven
 * @property double $LoanRepaidUptilNow
 * @property double $TotalValueOfLoanOutstanding
 * @property double $CashInHand
 * @property double $CashInBank
 * @property double $ClosingBalance
 *
 * @property Employee $emp
 * @property Groupdetails $group
 */
class Groupsavingdetails extends \yii\db\ActiveRecord
{
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return 'groupsavingdetails';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['EmpId', 'GroupId', 'Year', 'Month', 'OpeningBalance', 'TotalSaving', 'LoanRecovery', 'LoanInterest', 'Fine', 'BankInterest', 'Expenses', 'LoanGiven', 'BankLoan', 'TotalValueofLoanGiven', 'LoanRepaidUptilNow', 'TotalValueOfLoanOutstanding', 'CashInHand', 'CashInBank', 'ClosingBalance'], 'required'],
            [['EmpId', 'GroupId'], 'integer'],
			[['Year'],'validateyear'],
			[['Year'],'integer'],
            [[ 'Month'], 'string'],
			[['Month'],'validatemonth'],
            [['OpeningBalance', 'TotalSaving', 'LoanRecovery', 'LoanInterest', 'Fine', 'BankInterest', 'Expenses', 'LoanGiven', 'BankLoan', 'TotalValueofLoanGiven', 'LoanRepaidUptilNow', 'TotalValueOfLoanOutstanding', 'CashInHand', 'CashInBank', 'ClosingBalance'], 'number'],
            [['EmpId'], 'exist', 'skipOnError' => true, 'targetClass' => Employee::className(), 'targetAttribute' => ['EmpId' => 'EmpId']],
            [['GroupId'], 'exist', 'skipOnError' => false, 'targetClass' => Groupdetails::className(), 'targetAttribute' => ['GroupId' => 'GroupId']],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'GroupSavingDetailsId' => 'Group Saving Details ID',
            'EmpId' => 'Emp ID',
            'GroupId' => 'Group',
            'Year' => 'Year',
            'Month' => 'Month',
            'OpeningBalance' => 'Opening Balance',
            'TotalSaving' => 'Total Saving',
            'LoanRecovery' => 'Loan Recovery',
            'LoanInterest' => 'Loan Interest',
            'Fine' => 'Fine',
            'BankInterest' => 'Bank Interest',
            'Expenses' => 'Expenses',
            'LoanGiven' => 'Loan Given',
            'BankLoan' => 'Bank Loan',
            'TotalValueofLoanGiven' => 'Total Valueof Loan Given',
            'LoanRepaidUptilNow' => 'Loan Repaid Uptil Now',
            'TotalValueOfLoanOutstanding' => 'Total Value Of Loan Outstanding',
            'CashInHand' => 'Cash In Hand',
            'CashInBank' => 'Cash In Bank',
            'ClosingBalance' => 'Closing Balance',
        ];
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getEmp()
    {
        return $this->hasOne(Employee::className(), ['EmpId' => 'EmpId']);
    }

	public function validateyear($attribute,$params,$validator)
	{
	    
	$group = Groupdetails::find()->where(['GroupId' => $this->GroupId])->all();
	  $year;
	  if ($group !=null) {
								
			foreach($group as $groups)
			{
			  $year= $groups['FormationDate'];
			}
			  $year1= explode('-', $year);
			  if($this->$attribute<$year1[0])
				  $this->addError($attribute,'Year should be greater than or = to '.$year1[0]);		
	  }

    else
		   {
			  $this->addError($attribute,'Such group not there');
			}
			
			              
    }

	public function validatemonth($attribute, $params, $validator)
    {
        $group = Groupdetails::find()->where(['GroupId' => $this->GroupId])->all();
		$year;
		 if ($group !=null) {
              foreach($group as $groups)
			{
				  $year= $groups['FormationDate'];
			}
			$year1= explode('-', $year);
            if($this->Year==$year1[0]&&$this->$attribute<$year1[1])
				$this->addError($attribute,'Month should be greater than or = to '.date("F",strtotime($year)));	
    }
	else
		{	
	      $this->addError($attribute,'Such group not there');
         }
	}

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getGroup()
    {
        return $this->hasOne(Groupdetails::className(), ['GroupId' => 'GroupId']);
    }
}

My controller actionCreate() is

public function actionCreate()
    {
        $model = new Groupsavingdetails();

		if (Yii::$app->request->isAjax && $model->load(Yii::$app->request->post())) {
    Yii::$app->response->format = Response::FORMAT_JSON;
    return ActiveForm::validate($model);
}
		


        if ($model->load(Yii::$app->request->post())) {




	$count = Yii::$app->db->createCommand('SELECT count(*) 
 FROM groupsavingdetails
 where groupsavingdetails.GroupId='.$model->GroupId.'
	 and groupsavingdetails.Year<='.$model->Year.'
 and groupsavingdetails.Month<='.$model->Month
	 )
	 
 ->queryScalar();

 if($count==0)
{
 $model->OpeningBalance=0;
 $model->TotalValueofLoanGiven=0;
 $model->LoanRepaidUptilNow= 0;
 $model->TotalValueOfLoanOutstanding = 0;

 $model->ClosingBalance=($model->OpeningBalance+$model->TotalSaving+$model->LoanRecovery+$model->LoanInterest+$model->Fine+$model->BankInterest-$model->Expenses-$model->LoanGiven-$model->BankLoan);


 $model->save();
 
 }



return $this->redirect(['view', 'id' => $model->GroupSavingDetailsId]);




            
        } else {
            return $this->render('create', [
                'model' => $model,
            ]);
        }
    }

How should I resolve this?

The answer is in the error message.

In the 5th line of the SQL, you are comparing β€œMonth” using β€œ>=”, but the operand is missing. So it will end in a syntax error.

The problematic SQL was created here:

if ($model->load(Yii::$app->request->post())) {
    $count = Yii::$app->db->createCommand('SELECT count(*) 
        FROM groupsavingdetails
        where groupsavingdetails.GroupId='.$model->GroupId.'
        and groupsavingdetails.Year<='.$model->Year.'
        and groupsavingdetails.Month<='.$model->Month
    )->queryScalar();

$model->Month was empty then.

But you have to note that this code is vulnerable to SQL Injection attack, because it is using the attributes of $model without validating them. It’s quite dangerous.

Please check the following section of the guide:
Security > Best Practices > Avoiding SQL Injections

2 Likes