0

I am mostly working with Php 5.4+ and MySql 5.5+ versions. I am using Codeigniter for all the projects.

The problem i am facing is

Sometimes(on some production servers) when ever a POST variable contains ' or " quotes, a Database error occurs. But few times(on other servers) they work properly, i mean the quotations get inserted into tables

Though php and CI have a good facility for handling these strings with addslashes and mysql_escape_sequence etc

  1. It is not that easy to check these conditions for every possible variable that is being posted by the User

  2. Every time we have to use addslashes Ex: It\'s and while giving the output we have to again apply stripslashes to output It's. But it is difficult to handle for large values.

  3. As the Database saves the data as It\'s it is difficult to search for these strings.

For struggling for days, i found that using utf8mb4_general_ci advantageous over utf8

Accordingly i made sure the CI's Database.php have the following 

    $db['default']['char_set'] = 'utf8mb4';
    $db['default']['dbcollat'] = 'utf8mb4_unicode_ci';

Also i changed the datatype for respective columns to "LONGTEXT" and its collation to "utf8mb4_general_ci"

To my surprise they worked for some servers.

But Still on some servers i found the same problem. which is bit frustrating even though i made sure the server configuration matches with those working servers.

How all php and mysql developers are working with this Scenario? what precautions are you taking?

Please suggest!!

Ramaraju.d
  • 1,301
  • 6
  • 26
  • 46
  • 2
    If you are facing this problem then you are not using prepared statements! You should fix this immediately. – Jason Sperske Apr 08 '14 at 16:26
  • "Though php and CI have a good facility for handling these strings with addslashes and mysql_escape_sequence etc" — Those facilities are not good; they are "Awful" and "Poor and out of date" respectively. – Quentin Apr 08 '14 at 16:30
  • 2
    Read [How can I Use Prepared Statements in CodeIgniter](http://stackoverflow.com/questions/14156421/how-can-i-use-prepared-statements-in-codeigniter). It isn't exactly a duplicate of your question, but the answer is also the answer to your question. Also, consider switching to a different framework that supports real prepared queries with parameters. – Bill Karwin Apr 08 '14 at 16:31

1 Answers1

3

On my old projects, I just have a function DB::esc() that wraps whatever escape function goes to the library I'm using, be it mysql_real_escape_string or whatever else.

On my new projects, I use prepared statements and let the extension handle it.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • 1
    +1 just to clarify, ... "use prepared statements" **with bind variables**. (Just to point out that it's the change in the SQL text, and supplying values through bind variable references rather than including them in the SQL text. That is, one could use a prepared statement, but still include values in the SQL text, and the same problem would still exist.) – spencer7593 Apr 08 '14 at 16:33