Sql Injection Problem

Hi, I have a "General Ledger Accounts" table called GLaccnt.

For some of the records (accounts) - such as assets - I can use the name which is stored in the record (GLaccnt_name).

But for other records - such as clients - I have to get their personal info in a separate table called Client.

In the search() function of GLaccnt model, I cannot use “with” statements and eager loading, because not all GLaccnt records have a related record in Client, resulting in the gridview throwing ‘trying to get property of non-object’ errors - for some records.

So I use a select statement in the dataprovider criteria:

if (GLaccnt has a Client) then use Client.Client_name else use GLaccnt_name


$criteria->select = '

    *,

	(IF(GLaccnt_forgnkey > 0, 

        (

			SELECT	Client.Client_name

			FROM	Client

			WHERE	Client.Client_id = GLaccnt_forgnkey

		),

        GLaccnt_name

    )) AS myName';

The problem is that this sql is open for injection attacks, but I can’t do this:


... WHERE	Client.Client_id = :fk


$criteria->params=array(':fk' => $this->GLaccnt_forgnkey);

because the value of GLaccnt_forgnkey must change for every record, while $this->GLaccnt_forgnkey was set only once in the controller.

This also does not work:


$criteria->params=array(':fk' => 't.GLaccnt_forgnkey');

I want to sort/filter on this myName column, so I have to include it in the dataprovider. Setting the column’s ‘value’ in the CGridView by using a function, will thus also not work.

Any other ideas to prevent the injection?

That can be prevented relatively easily, just create a virtual attribute for client name in GLaccnt model:




public function getClientName()

{

	return $this->client ? $this->client->name : '';

}



Then you can add a column for clientName attribute in gridview and use "with" safely.

Why not?

It’s old plain LEFT JOIN, so for the records that don’t have related entry you have NULL.

Moreover, you can define a getter in your GLaccnt model, that returns either GLaccnt_name or GLaccnt->clent->name, depending on record type.

UPD phtamas was quicker.

Okay thanx. Will try.

Yes, maybe I forgot to elaborate on this:

The db design is a bit weird and abnormal. As I said, not all accounts belong to clients - which would normally result in account being parent and client being child. BUT, some Clients are also Suppliers - meaning they have more than one account. (If you don’t do it this way, then the user will have to keep separate identical records for the Client and Supplier, being the same person).

So now Client/Supplier is actually the parent table while account becomes the child table.

Client/supplier 0/1----o/many GLaccnt.

While you would normally read the parent record and then join the child records - in this specific CGridView, I display all the child records and then try to get the parents info - for those who have parents.

Maybe I should stop trying to be nice to users and just split Client/Supplier into separate tables. That should get things back to normal one-many.