2

I have about 273 items in a MySQL IN() list, and I believe this is causing a problem with getting all of the information I need for these items, and not sure how to change this query to just 1 or atleast less than 273 individual queries. Basically I have a list of string names that I need to query against and get the Name and ListID where Name is inside of a 273 count array:

$result = $wpdb->get_results('
    SELECT Name, ListID
    FROM ' . $wpdb->prefix . 'quickbook_items
    WHERE Name IN ("' . implode('", "', array_map('addslashes', $part_names)) . '")
    ORDER BY NULL', ARRAY_A);

I feel like I have read somewhere that using queries in Wordpress with IN could be problematic if the list is too large, in that Wordpress will not grab all data. But I don't remember if this is a Wordpress $wpdb limitation or a MySQL limitation. In any case, I am NOT getting all of the 273 rows returned, so I'm really not sure how to perform this query, while keeping db hits lower than 273 times (which would occur if I put the query inside of a loop, looping through $part_names.

Anyone have a suggestion on how to query Name and ListID from the quickbook_items table as shown above? Could really use some help here, or if there is a way to not have a limit of characters on a query set, to achieve this?

Paul Campbell
  • 1,906
  • 2
  • 12
  • 19
Solomon Closson
  • 6,111
  • 14
  • 73
  • 115
  • stackoverflow says [no limit](https://stackoverflow.com/questions/4275640/mysql-in-condition-limit) in mysql – Adder Mar 21 '18 at 09:00
  • Possible duplicate of [MySQL IN condition limit](https://stackoverflow.com/questions/4275640/mysql-in-condition-limit) – Racil Hilan Mar 21 '18 at 09:03
  • use LOWER() or UPPER() function to match name. may be case issue. or issue like maxlee's = maxlee\'s – rajeev Mar 21 '18 at 09:11
  • Offtopic: you'll probably not want use addslashes, but mysqli_real_escape_string or Pdo::quote . See https://stackoverflow.com/questions/3473047/mysql-real-escape-string-vs-addslashes?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa addslashes() is not suitable for true escaping for MySQL (for example hexadecimal representation might be used) – twicejr Mar 21 '18 at 09:16

1 Answers1

7

IN clause and max_allowed_packet

There's no defined limit on the number of items within IN (...).

However, the length of the SQL string itself (in bytes) is limited by the value of the max_allowed_packet system variable.

Here an excerpt from the MySQL documentation:

The number of values in the IN list is only limited by the max_allowed_packet value.

The default value of max_allowed_packet is 4 MB, so unless you had changed this value, it's extremely unlikely that this is the problem. Besides, hitting this limit would have caused an error rather than wrong results.

addslashes

It's really not a good idea to use addslashes to build MySQL string literals. Consider using mysqli_real_escape_string for this purpose.

A suggestion

Could you please do the following to analyze the issue. Build a loop over $part_names, and execute the very same statement (incl. implode) - but for just one name in every iteration. Then check whether every iteration returns a non-empty result.

This way you would get the names which aren't returned and it can give you some clues.

Another possible outcome would be that all names are returned - in this case the problem could indeed be with the number of items within the IN clause.

Alex Shesterov
  • 26,085
  • 12
  • 82
  • 103
  • https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet – Ravinder Reddy Mar 21 '18 at 09:22
  • 1
    Perhaps I've misunderstood, but this seems different from the sql injection prevention recommended by WordPress themselves. https://codex.wordpress.org/Class_Reference/wpdb#Protect_Queries_Against_SQL_Injection_Attacks – Strawberry Mar 21 '18 at 09:41
  • Wordpress adds magic quotes even if you have it turned off in php. `addslashes` is important for this feature which is only available to the Wordpress Administrators anyways and that would be a total of 2 people. – Solomon Closson Mar 21 '18 at 15:03
  • I thought I remember reading that the Wordpress class `$wpdb` functions puts a limit on the total number of characters allowed inside of these functions. A co-worker of mine has even confirmed this for me before. – Solomon Closson Mar 21 '18 at 15:07
  • Ok, guys, it seems that wpengine is killing the query as it is too long in characters (this is ridiculous): https://wpengine.com/support/troubleshoot-wordpress-wp-engine-error-log/#killedquery – Solomon Closson Mar 21 '18 at 16:39
  • I am currently in the process of moving all of my sites out of the wpengine outrageous prices and limitations. – Solomon Closson Mar 21 '18 at 16:42