We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Models, sanitizing & sql injection

Hi all,

I've just read this part of the doc regarding sql injections (https://docs.phalcon.io/en/latest/reference/models.html#avoiding-sql-injections), and that's really nice to have the models sanitizing the data automatically.

Pronlem is, I'm generating automatically my models and metaData base on my db structure, and I haven't seen any way to "cast" or define the field size as it's done here:

$sth->bindParam(':productTypesId', $productTypesId, PDO::PARAM_INT);
$sth->bindParam(':name', $name, PDO::PARAM_STR, 70);
$sth->bindParam(':price', doubleval($price));
$sth->bindParam(':active', $active, PDO::PARAM_STR, 1);

for reference, here is my xcurrent version of the metaData:


public function metaData()
    {
        return array (
        Meta::MODELS_ATTRIBUTES => 
        array (
                0 => 'clientpk',
                1 => 'account_managerfk',
                2 => 'primary_contactfk',
                3 => 'company_name',
                4 => 'credit',
                5 => 'note',
                6 => 'firstname',
                7 => 'lastname',
                8 => 'email',
                9 => 'phone',
                10 => 'phone2',
                11 => 'date_created',
                12 => 'date_updated',
                13 => 'created_by',
                14 => 'updated_by'
        ),
        Meta::MODELS_DATA_TYPES => 
        array (
                'clientpk' => Col::TYPE_INTEGER,
                'account_managerfk' => Col::TYPE_INTEGER,
                'primary_contactfk' => Col::TYPE_INTEGER,
                'company_name' => Col::TYPE_VARCHAR,
                'credit' => Col::TYPE_INTEGER,
                'note' => Col::TYPE_VARCHAR,
                'firstname' => Col::TYPE_VARCHAR,
                'lastname' => Col::TYPE_VARCHAR,
                'email' => Col::TYPE_VARCHAR,
                'phone' => Col::TYPE_VARCHAR,
                'phone2' => Col::TYPE_VARCHAR,
                'date_created' => Col::TYPE_DATETIME,
                'date_updated' => Col::TYPE_DATETIME,
                'created_by' => Col::TYPE_INTEGER,
                'updated_by' => Col::TYPE_INTEGER
        ),
        Meta::MODELS_DATA_TYPES_NUMERIC => 
        array (
                'clientpk' => true,
                'account_managerfk' => true,
                'primary_contactfk' => true,
                'credit' => true,
                'created_by' => true,
                'updated_by' => true
        ),
        Meta::MODELS_DATA_TYPES_BIND => 
        array (
                'clientpk' => Col::BIND_PARAM_INT,
                'account_managerfk' => Col::BIND_PARAM_INT,
                'primary_contactfk' => Col::BIND_PARAM_INT,
                'company_name' => Col::BIND_PARAM_STR,
                'credit' => Col::BIND_PARAM_INT,
                'note' => Col::BIND_PARAM_STR,
                'firstname' => Col::BIND_PARAM_STR,
                'lastname' => Col::BIND_PARAM_STR,
                'email' => Col::BIND_PARAM_STR,
                'phone' => Col::BIND_PARAM_STR,
                'phone2' => Col::BIND_PARAM_STR,
                'date_created' => Col::BIND_PARAM_STR,
                'date_updated' => Col::BIND_PARAM_STR,
                'created_by' => Col::BIND_PARAM_INT,
                'updated_by' => Col::BIND_PARAM_INT
        ),
        Meta::MODELS_PRIMARY_KEY => 
        array (
                0 => 'clientpk'
        ),
        Meta::MODELS_IDENTITY_COLUMN => 'clientpk',
        Meta::MODELS_AUTOMATIC_DEFAULT_INSERT => array(),
        Meta::MODELS_AUTOMATIC_DEFAULT_UPDATE => array(),
        Meta::MODELS_DEFAULT_VALUES => array(),
        Meta::MODELS_EMPTY_STRING_VALUES => array(),
        Meta::MODELS_NON_PRIMARY_KEY => 
        array (
                0 => 'account_managerfk',
                1 => 'primary_contactfk',
                2 => 'company_name',
                3 => 'credit',
                4 => 'note',
                5 => 'firstname',
                6 => 'lastname',
                7 => 'email',
                8 => 'phone',
                9 => 'phone2',
                10 => 'date_created',
                11 => 'date_updated',
                12 => 'created_by',
                13 => 'updated_by'
        ),
        Meta::MODELS_NOT_NULL => 
        array (
                0 => 'company_name',
                1 => 'credit',
                2 => 'firstname',
                3 => 'lastname',
                4 => 'email'
        ));
    }

Could anyone let me know how to integrate size/length controls and casting to my models. Thanks.

Models use prepared queries for saving, so a lot of the usual sanitization isn't necessary.

Hi,

I get that, that's great and that's what I want to use. What I'd like to know is how to configure my models (with my autogenerated metaData and/or other settings) so they automatically run these validations.

For instance, I assume I'm bindind correctly the column type in the metadata, but I don't know how and where to define the size/length as shown in the doc:

$sth->bindParam(':name', $name, PDO::PARAM_STR, 70);

Is it just no possible to do so without manually binding params ? In that case, I can always update the script that generates my modes to add lines like the above, but I'm not really sure how to implement that at the model level...

You may be able to tie in to the Validation functionality - but you usually have to manually set up this validation. I suppose you could write some code to iterate through generic meta-data. I don't think the meta-data stores the field length though - so you're back to manual validation.

https://docs.phalcon.io/en/latest/reference/models.html#validating-data-integrity

Let me get back to the core of my problem, because this discussion is not going anywhere...

  1. I'm working on the model validation, not only on the sql injection part
  2. I'm building custom validation classes for each of my models
  3. I understand the models (or prepared queries) are validating automatically the fields, and I just want to avoid checking the fields twice.

The core of the problem is this sentence: "The good news is that Phalcon do this for you automatically" from https://docs.phalcon.io/en/latest/reference/models.html#avoiding-sql-injections.

I don't know how to "configure" my models, or what to add to my metaData to make my model automatically test something like

$sth->bindParam(':name', $name, PDO::PARAM_STR, 70);

I do know how to bind a var type to the field, but I can't find anywhere how to define the field size/length. From my meta data you can see the company_field is string (varchar 255), and I'd like my model to be able to test :

$sth->bindParam(':company_name', $name, PDO::PARAM_STR, 255);

I hope I'm clear enough. Cheers

I really don't think you can do exactly what you're wanting. I've not seen anywhere where Phalcon will do any validation other than checking for NULL values. If you want this granularity of validation, you're going to have to write your own method based on the model's meta-data.