In my previous article, Using LIKE with Variables and Patterns in SQL, we explored how the LIKE predicate behaves in different scenarios, from Embedded SQL to Dynamic SQL, and what happens to performance when wildcards and variables come into play. That piece was about getting comfortable writing a working LIKE query. But writing SQL that works is only the starting point. To build applications that are reliable, scalable, and secure, you need to understand the best practices that underpin all SQL, including queries that use LIKE.

This article takes the next step. We’ll look at a few key points to help strengthen your SQL code, avoid common pitfalls, and make sure your SELECT statements run not just correctly, but also efficiently and safely. I'll use SELECT statements with LIKE predicate as an example along the way, showing how these broader principles directly affect your queries and their results.

*This is what Gemini came up with for this article, kinda cute.

Safety and Security

First and foremost, a high-performance application is meaningless if it is not secure. When using LIKE with user-supplied input, the most significant threat is SQL Injection, which can compromise the integrity of your results and the security of your entire database.

Let me remind you that SQL Injection is currently ranked as A03:2021 – Injection, which places it at #3 on the OWASP Top 10 list for 2021. Although the 2021 OWASP update moved it from the top spot to third, this threat remains relevant and impactful, albeit not the most prevalent issue. Another change was that the scope was broadened from just SQL Injection to Injection in general, encompassing SQL, NoSQL, OS commands, and others. And it was flagged in 94% of tested applications, with an average incidence rate of 3.37%, making it one of the more common vulnerability areas.

So, how can you protect yourself from it? The other day, one of the members posted a question on the Community with a code sample that was vulnerable to precisely this. They passed user input directly as part of the query string, thereby giving an attacker the opportunity to inject malicious SQL code into the query, alter it, and potentially gain unauthorized access or modify data. 

This issue is covered in several articles, recently published mine in French ( I completely forgot that I haven't translated it into English 👼) and @Ash.Sherzhanov's in English. Long story short, avoid concatenating user input directly into the SQL statement. So, DO NOT do this:

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

In this case, the attacker can use the input to pass a malicious piece of code, for example:

 SET input = "' or 1=1 --"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE '%"_input_"%'")
 SET rs = statement.%Execute()
 DO rs.%Display()

Essentially, the attacker can gather all the information about the bank's clients without any prior knowledge.

Instead, use the placeholder "?":

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute("%"_input_"%")
 DO rs.%Display()

Best Practices: 

• Use prepared parameters. They separate the query structure from the input values, thus prohibiting attackers from injecting malicious SQL code. In Dynamic SQL, it's a placeholder "?", in Embedded SQL, it's a named parameter preceded by ":".
• User input validation. Validating user input before inserting it into SQL queries is another important preventative measure. Use regular expressions or appropriate validation functions to check the validity of the data.
• Use patterns in class descriptions. This is similar to the previous point, but allows you to verify input when adding new instances.
• Special Character Filtering. Filtering out special characters that can be used to inject SQL code can also help prevent attacks. Use appropriate filtering functions to remove or escape potentially dangerous characters.
• Use Stored Procedures. Stored procedures are database objects that encapsulate a set of SQL statements, allowing for efficient execution and reuse. By using stored procedures, you can centralize your application logic and reduce the risk of SQL injections.
• Regularly Update Software Components. Ensure that you regularly update InterSystems IRIS and its related software components to benefit from the latest security patches.

Performance

A performant application can handle high volumes of data and concurrent users without delay. At the heart of this performance lies the efficiency of your SQL queries. A slow query might time out, or in a concurrent system, it might lock resources and produce inconsistent results. For a query using the LIKE predicate, the difference between a fast and a slow execution plan can completely alter the user experience and the reliability of the data returned.

1. We are all familiar with indexes and their ability to significantly improve query performance. However, apart from having the correct indexes, we also need to utilize them effectively. For a LIKE query, the presence and proper design of an index, as well as its usage, determine whether the database performs a quick search or a time-consuming full-table scan.

If we look at the example above, it's not very index-friendly. When a LIKE pattern begins with a wildcard, a standard B-tree index cannot be used to narrow the search. The database is forced to scan every single row in the table to find a match. For large tables, this results in a slow query that may appear to hang and could even time out, effectively preventing you from obtaining any results at all. Therefore, DO NOT write queries like this:

SELECT * FROM Banking.Person WHERE name LIKE '%Smith%'

This will require a full table or index scan to find "Smith" anywhere in the name, including both first and last names.

Instead, if you know the first letters of the string you're looking for, provide them without a wildcard (% or _) in the beginning:

SELECT * FROM Banking.Person WHERE name LIKE 'Smith%'

Better yet, use the %STARTSWITH predicate instead of LIKE:

 SET input = "Smith"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name %STARTSWITH ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

In this case, the database can perform a fast index scan, drastically reducing query time. This ensures a quick retrieval of results.

A little interesting tidbit for you, by default, %STARTSWITH and LIKE string comparisons are not case-sensitive. So the following query will return the same result:

 Set input = "smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE name LIKE ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

2. Another way to influence query performance is to use the proper collation for a string property. IRIS offers several collations, with the most common being:

• SQLUPPER: The default collation for most string data types, it performs case-insensitive comparisons by internally converting both the column value and the pattern to uppercase before comparison. While convenient, this internal operation can introduce a small performance overhead compared to a direct binary comparison.
• SQLSTRING: This collation performs a case-sensitive, exact binary comparison. It is often faster for equality and LIKE comparisons where case-sensitivity is required, as it avoids the internal conversion step.

If case-sensitivity or specific linguistic sorting is required, explicitly define the collation for the column or use SQLSTRING in the LIKE clause. This prevents the database from performing an unexpected or inefficient case-insensitive comparison.

If, for some reason, you can't / don't want to apply collation to the property itself, you can always use the SQL collation function %SQLSTRING. This converts the expression to a format that is sorted as a case-sensitive string. %SQLSTRING strips trailing whitespace (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string.

Therefore, if you want to find all "Smith"s and not "smith" or any other kind of "sMith", you can use %SQLSTRING directly in your SQL statement:

 SET input = "Smith%"
 SET statement = ##class(%SQL.Statement).%New()
 DO statement.%Prepare("SELECT * FROM Banking.Person WHERE %SQLSTRING name LIKE %SQLSTRING ?")
 SET rs = statement.%Execute(input)
 DO rs.%Display()

This example uses the %SQLSTRING format to make this comparison case-sensitive. It returns all names that begin with “Smith”. Note that in this case, you should apply %SQLSTRING collation to both sides of the statement. However, if the column itself already has SQLSTRING collation defined in the class property, you only need to apply %SQLSTRING to the literal or variable being compared, as the column is already in the desired collation. If the column has SQLUPPER (the default), then applying %SQLSTRING to both sides correctly overrides the column's default.

Best Practices: 

• Be mindful of wildcards. If at all possible, try to avoid using them as the first symbol in the search string.
• Get the best use of collation. Consider your most common queries and set up collation either directly in the class description or in the query itself.

To sum up, the LIKE predicate is a versatile and powerful tool for pattern matching. However, its effectiveness and reliability are intrinsically linked to broader principles of performance and security. A LIKE query with a leading wildcard might fail to return results in a timely manner, while a vulnerable LIKE query can be manipulated to return an entirely different set of results than intended. By combining the syntax and variable-handling techniques from the previous article with the best practices of indexing and a steadfast commitment to parameterized queries, you can ensure that your LIKE statements are not only functional but also fast, secure, and reliable, consistently providing your application with the correct results.