SQL Injection Question

Hi guys,

I use the following Code to load a model in my Controller:

	public function loadModel($id)




			throw new CHttpException(404,'The requested page does not exist.');

		return $model;


When Calling: my.site/index.php?r=image/view&id=582454545

I get the following Output:

Error 404

The requested page does not exist.

However when calling my.site/index.php?r=image/view&id=582gg

I get the following error message:

Error 500

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column '582gg' in 'where clause'

Why is the Error 500 coming up?

I thought the "loadModel($id)" was supposed to catch exceptions like this?

Is there a way to suppress this output to the user?

Is this dangerous in regard to SQli Attacks?

What is your yii version?

582gg is not an int, is it? :)

That’s one error.

Since you’ve provided way too little information, I can’t help you further.

I use Yii 1.6

582gg is not an int… that is clear. However it is always a bad idea to show back SQL error messages to the user.

The loadModel($id)-action comes per default in every auto generated-Yii Controller.

That means SQL error messages are shown back to the user per default. (I’m not in Debug Mode…!!)

How can I circumvent this??

And the second question: can an attacker exploit the loadModel($id)-action?



Hi Andreas,

I agree that SQL errors or any internal errors should be kept away from the users. So when you say you are not in debug mode, did you mean that you have commented out the YII_DEBUG portion in your index.php?If not, comment those lines and you should not see any details when the error is prompted.



// remove the following lines when in production mode <-- 

defined('YII_DEBUG') or define('YII_DEBUG',true); 

// specify how many levels of call stack should be shown in each log message

defined('YII_TRACE_LEVEL') or define('YII_TRACE_LEVEL',3);


Your function loadModel is designed to receive an INT id, so maybe you can try removing the ‘(int)’ part of the loadModel to see if the exception can handle even if you pass 582gg as a value of your primary key.

As to your question about SQL attacks, I don’t think a hacker can inject something in loadModel, since it is only querying. Maybe someone who has more knowledge about this can explain it to us better :)

The (int) $id expression in loadModel() always casts value “582gg” to 582 but the error message talks about “column 582gg” which means that loadModel() wasn’t called in the second case but some unwanted action was. It indicates a routing problem, check your routing rules (id=582gg doesn’t match <id:\d+>).

You can write an error action.

In yii-1.1.7 with debug mode off it doesn’t provide database error message. It just print something like this:

CDbCommand failed to execute the SQL statement.

And stop. But I test it in yii-1.1.2 and it display this bad SQL message.

I don’t know when this was fixed.

Not fixed as far as I can tell, gives more information that that:

Here’s the output to the browser for an sql error on the latest stable version yii-1.1.8.r3324 with YII_DEBUG commented, i.e., debug mode off:

Error 500

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.position_id' in 'where clause'

and here is why it is doing that from the execute command in CDbCommand.php in the framework:

326                 catch(Exception $e)

 327                 {

 328                         if($this->_connection->enableProfiling)

 329                                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().')','system.db.CDbCommand.execute');

 330             $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;

 331             $message = $e->getMessage();

 332                         Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',

 333                                 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');

 334             if(YII_DEBUG)

 335                 $message .= '. The SQL statement executed was: '.$this->getText().$par;

 336                         throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',

 337                                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);

 338                 }

Notice that even if YII_DEBUG is not set the $e->message is still displayed. When YII_DEBUG is set, the full sql statement is also tacked onto the end.

You may need to derive your own class from CDbCommand to change this behavior.

Full reply to this topic is phtamas#6.

And for extending CDbCommand… not agree with you.

Instead as explained here Handling Errors Using an Action process error properly and display you own message to end user. How? Very simple:

switch ($error['code'])



    case 500:

        $error['message'] = 'Ouch!';





hm strange that it says that the column dont exists…

thePk should search the PK and not column… maybe you have some model with strange columns… ::)

I agree extending CDbCommand is the wrong approach. I should not have to even consider doing that.

Interestingly enough right above that handling errors entry is a section that reads

Info: When the application runs in production mode, all errors including those internal ones will be displayed using view errorXXX. This is because the call stack of an error may contain sensitive information. In this case, developers should rely on the error logs to determine what is the real cause of an error.

So you can see the intent to not display sensitive information is there, it just didn’t quite get implemented that way. I expect that when an sql error occurs in production mode that the default error display mode will be to display only error 500 with no details about why the error occurred. Then if I want more information displayed that is when I should add error handlers. The default should be to show the least. :)

thanks but what you are seeing is an intentionally introduced database error to illustrate the issue… :)

Again not agree with you :)

Yii gives full freedom to developers. It gives you all relevant data about errors.

Its up to you to show what you want. Let’s say you have application running in production mode and Yii provide some poor error message. But you, as a developer, needs to know when some error occurred. You want eg to store error message in database, or eg email error message to administrator. And assume for performance reasons you disabled CFileLogRoute. How would you know error message?

So solution is simple as I described in previous post.

Catch the error, show some fancy message to user, and for yourself - keep tracking everything.

You should properly configure your ini file so PHP don’t display PHP errors to user. It’s not error_reporting(0). Study that for a while, and one interested feature of Yii’s error handling - PHP errors (yes errors not exceptions) are automatically assigned error code 500 :)

See more information here

really? you disagree with errors being off by default for sql but not for php! :)

Perhaps you may have misunderstood my intent here. I have one and only one issue on this thread, the display of SQL error information by default. I was initially disagreeing with machinagin’s assertion that sql detail display had been fixed and agreeing with macinville’s post that sql errors should be kept away from users.

I am not, nor have I in this post, suggested anything about developer freedom. That’s your words, not mine.

I also have no truck with error 500 being displayed. That’s a standard HTTP error.

Since you linked

to an error 500 page that doesn’t show internal error details it’s not clear what you disagree with.

It’s information tacked onto the end of the 500 about the SQL error, even that there was one, being displayed by default that I take issue with. My stance is that internal error reporting, of any kind, should be OFF by default on production systems.

A developer still has just as much control if internal error messages are OFF by default. The developer can change that or not afterwards, as desired.

BTW the topic here is not PHP errors, they are irrelevant to the subject at hand which is SQL errors. The only relevancy is that php.net recommends for PHP what I recommend as a default for SQL and all internal errors:

:) Cheers!


Seems like you didn’t understand me what I intend to say.

If you follow recommendations from php.ini file and turn off displaying errors (its all clear written in that file) what is result of that? If PHP Error occurs you will get blank screen. Nothing at all. Instead of blank screen you can still show some more user friendly screen to end users. With the usage of that 500 error code from a link I posted, you can process it in a switch statement as well as all other exceptions thrown(eg 404, 403, 400…).

So my resume is, Yii will give you full information about error, you can process it and finally display your own message to end users. That’s what I meant when I (I said that, not you :) ) said developers freedom.

One simple example.

// (1) somewhere is thrown CDbException with error code 500 (Yii throws it - actually PDO :-) if you want more precision )




// (2) somewhere an PHP Error occurred with error code 500 as shown on my link


$variable = $model->property; // Trying to get property of non-object let's say for example


// (3) somewhere you throw CHttpException with error code 404


throw new CHttpException(404); // message will be assigned in switch statement


// somewhere in your error action


switch ($error['code'])


    case 400:

        $error['message'] = 'Bad Request...';


    case 403:

        $error['message'] = 'Access Denied...';


    case 404:

        $error['message'] = 'Not Found...'; // here is CHttpException (3)


    case 500:

        $error['message'] = 'Internal Error...'; // here is CDbException (1) and all PHP Errors (2)



        $error['message'] = 'Some default message...';




// somewhere in your error view file


echo $error['message'];


Do you see something? You can catch for example $error[‘type’] - CDbException and write message Everything is perfectly ok…, but for other 500 errors you can write Internal Error blah blah blah

That is what I’m trying to say.

About link:

As you can see, Yii has great error handling system, because all errors (Exceptions and PHP Errors) can be caught in one simple error action.

I have really nothing more to say, and I will always try to help.

I apologize to all members if these posts went too offtopic, but maybe someone found something useful in our posts.

Cheers ;)

Yes I agree, we are not understanding each other, so that’s it for me. Hope this has helped some.