0

I have custom tables in the database i.e. g_projects with loads of columns. Data is stored in the table using the Wordpress native insert query i.e. $wpdb->insert, hence they are escaped.

For example

Input: Application's Document

Database: Application\'s Document

The problem is how can I perform search on the escaped columns because they are stored as "Application\'s Document" whereas, I want to be able to search as "Application Document".

My query below returns nothing:

$wpdb->query("SELECT * FROM g_projects WHERE type=\"Application's Document\""); 

Edit #1

I am using POSTMAN add-on to call my API which then uses $wpdb->insert() method to insert new records. Upon further investigation I observed that the slashes were actually added by the POSTMAN somehow because If I pass data to API using POSTMAN it stores escaped strings. Whereas, if I use the $wpdb->insert() directly it will store string without slashes as suggested below by @TheDeadMedic

Edit #2

This is the final edit for those who faced similar problems using POSTMAN. POSTMAN actually escapes string before passing to the $wpdb->insert() function. Therefore, use $wpdb->unslash() on the $data before passing to the insert function.

  • Why are you storing the data escaped? You only need to escape during insert, which $wpdb->insert( $table, $data ) will already do for you. – TheDeadMedic Sep 23 '15 at 13:53
  • I am not escaping data. Please see above my input and how it is stored in database. – Ghazanfar Mir Sep 23 '15 at 14:29
  • What I'm saying is the value stored in the db should be Application's Document, not Application\'s Document. – TheDeadMedic Sep 23 '15 at 14:41
  • I was using POSTMAN add-on to pass data to an API which use $wpdb->insert() to add new records. It looks like POSTMAN somehow escapes the string because it added slashes to escape single quote – Ghazanfar Mir Sep 24 '15 at 11:45
  • @TheDeadMedic It looks like POSTMAN is escaping string that is why I have slashes in the database. – Ghazanfar Mir Sep 24 '15 at 11:51
0

I think what TheDeadMedic is trying to say, is your data in SQL should not have escapes. You will want to run queries to remove the escapes, but that can be complicated. Until that is done, you will not be able to search for "Application's Document" and get back any results.

When escaping strings into the database, your best option is to use prepared statements. It is the most secure way to avoid breaking SQL statements with the ' character, and it also prevents SQL injection.

However, if you are on an older server that doesn't support it, you will have to use an escaping function. In PHP, MySQL has the method mysql_real_escape_string($value) which will do it for you. Just wrap all your varibles in that. It's better than nothing.

  • mysql_* is obsolete. Please don't use it – Ghazanfar Mir Sep 24 '15 at 11:51
  • The point was to get him thinking in the right direction. As I mentioned above, prepared statements should be the route he goes which is MySQLi. the MySQL driver is for those on servers that do not have MySQLi (although I have no idea why anyone would stay on those servers). mysql_real_escape_string is better than nothing at all which is the only reason I mentioned it. – Dunimas Sep 24 '15 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.