The security flaw would — conceivably — arise if user input is put directly into the table without validation and users are limited to what they can see.
That is, if
'%' could allow someone to see data they shouldn’t.
However, using a column name for the like pattern is not a SQL injection risk, in the sense that it cannot cause another command to “inadvertently” run. And if you are putting the patterns into the table for matching purposes, there is no additional risk.
There might be a concern with performance, but that is another issue entirely.
There are no basic security flaws inherent in this practice. However, you might need to parse or strictly control the format of the input strings so you don’t end up with entries like these:
| ID | Identifier | | --------- |---------------------| | 8 | A% | | 9 | % |
Also note that it is unlikely that an attacker would choose to look for flaws in a pattern usage like this, since it is very uncommon.
Issues are possible if a new pattern of data inadvertently matches an existing filter string, incorrectly returning the new entry for the old filter. However, good data formatting practices should be able to prevent any issues like this.
I have used this type of technique quite extensively and did not have any performance issues or security issues due to this. The use case is typically for storing some rules so the data set has only a few rows to deal with – so performance is never an issue. Use cases where this is quite useful is, for example:
Having mapping tables during integrations. So A1, A2, A3 on system 1 need to be sent as X to other system. Using wild cards one row can be used.
I always put a sort sequence# to resolve conflict and resolving to inadventent value becomes bug/feature rather than a security flaw.
Another varient I have used quite often — where the engine needs to account for SQL Injtection type of attack — is to go a step further and put in conditions. So store a full PL/SQL or any other interpretted language condition like @a=”A” and @b = ‘2’ ….
Preventing against SQL Injection is easy but it ends up saving a lot of code.
So back to main question – the technique will work out just fine.