Switching from MySQL to PostgreSQl security issue


Initially i developed an application using MySQL database. It worked great. Now, I switched to PostgreSQL.

I’m little bit concern about input sanitisation when Yii2 uses PostgreSQL. For an example,

http://myapp.com//supplier/view?id=123 (works great both when I use MySQL or PostgreSQL)

http://myapp.com/supplier/view?id=xxx (where is xxx is some ugly params)

#When use MySQL

it throws 404 which is good

#when use PostgreSQ

It shows Database error. From log it seems when Yii uses PostgreSQL it doesn’t sanitize input variables. So, there could be risk of SQL injection. Here is an example of log trace

Next yii\db\Exception: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "xxx"

The SQL being executed was: SELECT * FROM “site” WHERE “site_id”=‘xxx’ in /var/www/myapp/vendor/yiisoft/yii2/db/Schema.php:636

I didn’t write any raw queroes. The view code is generated by Gii.

Any thoughts?


It’s because PosgresSQL doesn’t permit a string parameter (‘xxx’) for an integer column (‘site_id’), while MySQL permits it and returns an empty result. PosgresSQL is more strict on value types than MySQL.

So, there’s not a higher security risk in using PosgresSQL than MySQL in this case.