In current project using MySQL. Looking into the mysqli functions, found them to be very difficult to use. In my opinion, any programmer using the library should have a Medal of Honor.
Three problems:
mysqlidoes not handle NULL or functions such as CURRENT_DATE- it is impossible to use paramterized SQL statements for queries with random WHERE clauses (so how do you prevent SQL Injection attacks?)
- in my opinion, it's too much work to use
sqliin complicated cases (see link)
PDO does not solve any of these problems. The issue is SQL injection: how does PDO help with SQL Injection if you do not use paramaterized queries?
That in mind, wrote a little library for MySQL in PHP that does the job simply and effectively. See https://github.com/bangkok-maco/MySQL-sql-injection-prevention
(Someone helpfully put the library here but replacing it with link, but please use the REAME on the site for introduction.)
The problem is, I do know know whether the base tenets solve enough of the problem needing solving.
Here are the tenets:
- each SELECT is executed by a user with no DML (INSERT, UPDATE, DELETE) rights
- a DML statement returns only the number of affected rows
- sensitive data is inaccessible except through MySQL functions and procedures
- a shadow user and no one else may execute these functions and has no other rights
- DML statements are parameterized; SELECT statements are never parameterized
Logically, is this enough?
A SQL Injection can result in an illegal SELECT (which can be simply eaten), but that SELECT can write no data. DML is paramaterized, so that data is scalar. Sensitive data, eg, passwords, are simply not accessible through ordinary SQL or through ordinary users.
