"SELECT * FROM users WHERE id IN ()" == FAIL

Question

I have a function that I call sqlf (), it emulates prepared statements. For example, I can do things like:

  $ sql = sqlf ("SELECT * FROM Users WHERE name =: 1 AND email =: 2", 'Big "John"', ' [e-mail] ');

For various reasons, I cannot use the prepared instructions, but I would like to imitate them. The problem I am facing is with queries like

  $ sql = sqlf ("SELECT * FROM Users WHERE id IN (: 1)", array (1,2,3));

My code works, but it fails with an empty array, for example. The following causes the mysql error:

  SELECT * FROM Users WHERE id IN ();

Does anyone have any suggestions? How should I translate and empty an array in sql that can be entered into an IN clause? NULL substitution will not work.

Answer 1

Null - the only value you can guarantee is not included in the set. Why is this not an option? Everything else can be considered as part of a set of potentials, all of them are values.

Answer 2

I would say that passing an empty array as an argument to an IN () clause is an error. You have control over the syntax of the request when calling this function, so you should also be responsible for the input. I suggest checking the void argument before calling the function.

Answer 3

Is it likely to detect empty arrays with sqlf and modify SQL so as not to have an IN clause?

You can execute the SQL postprocess before passing it to the "real" SQL executor so that the "IN ()" sections are deleted, although you will have to do all kinds of tricks to see which other elements need to be removed so:

SELECT * FROM Users WHERE id IN (); SELECT * FROM Users WHERE a = 7 AND id IN (); SELECT * FROM Users WHERE id IN () OR a = 9; 

will become:

 SELECT * FROM Users; SELECT * FROM Users WHERE a = 7; SELECT * FROM Users WHERE a = 9; 

This can become complicated depending on the complexity of your SQL - you basically need a complete SQL interpreter.

Answer 4

If your prepared function simply replaces: 1 with an equivalent argument, you can try to make your request contain something like (': 1'), so if: 1 is empty, it resolves (''), which will not cause a syntax error analysis (however, this can cause undesirable behavior if this field can have empty values, although if it is int, this is not a problem). However, this is not a very clean solution, and you better determine if the array is empty, and simply use an alternative version of the query that does not have the "IN (: 1)" component. (If this is the only logic in the WHERE clause, then apparently you do not want to select everything, so you simply would not execute the query.)

Answer 5

I would use zero, assuming your column "id" is a pseudo-code that automatically assigns numbers.

As far as I know, automatic key generators in most database brands start with 1. This is an agreement, not a requirement (fields with automatic numbering are not defined in standard SQL). But this convention is quite common, and you probably rely on it.

Since zero probably never appears in the id column, you can use this value in the IN () predicate when your input array is empty and it will never match.

Answer 6

The only way I can do this is to do your scan of the sqlf() function to see if any particular substitution appears right after "IN (", and then if the passed variable is an empty array, add that- which you know for sure will not be in this column: "m,znmzcb~~1" , for example. It is a hack, but it will work.

If you want to take it even more, can you change your function so that there are different types of substitutions? It looks like your function is looking at a colon followed by a number. Why not add another type, like @, followed by a number that will be smart for empty arrays (this will save you from having to scan and guess if the variable should be an array).