Take the 2-minute tour ×

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:

  • mysqli does 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 sqli in 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.

share|improve this question
stackoverflow.com/tags/php/info –  ajreal Sep 13 '11 at 12:35
You think all that is easier than just using mysqli or PDO? –  Konerak Sep 13 '11 at 12:39
Can you please add a else if( $paramCt==29 ) case? –  Konerak Sep 13 '11 at 12:40
I have found mysqli quite easy to use - and it is OO. Seems rather odd to reinvent the wheel. –  Ed Heal Sep 13 '11 at 12:54
The learning curve for learning PDO/mysqli once is indeed steep at first, but you only have to do that once. Afterwards, it will server for all your future projects, so the amortized cost is pretty low. DIY-security usually is a bad idea and one of the worst areas to suffer from the not invented here syndrome. Plus, if you run into problems, getting help with mysqli is pretty easy, getting help with your own (by then monster-)class is hard. But it is your call in the end. –  Konerak Sep 13 '11 at 13:04
show 7 more comments

2 Answers

This code is as leaky as a sieve.
You are calling mysql_query in one function with no mysql_real_escape_string() in sight.
You are using PDO in another function.
This is a mess and makes no sense. If you know how to use PDO, just use that.
Then you will be safe.

If you want to use mysql_query, you have to use mysql_real_escape_string().

share|improve this answer
mysql_real_escape_string() is one of many sanitizing functions that should be used on data, but before library functions are called, because it's then you know the datatype and business rules. –  cc young Sep 13 '11 at 14:29
how exactly does PDO solve the issues raised here? mysqli being unable to have NULL parameters, logically being unable to use parameters in all cases as a defense against SQL Injection attacks. –  cc young Sep 13 '11 at 14:31
add comment

have asked this question entirely wrong. have reposted the question here.

share|improve this answer
add comment

Your Answer


