Writing Queries

When you're executing a query with an identifier such as $sqlite_query you should avoid writing the queries inside the identifier. The reason for this is because characters like (, ) and , won't work properly, for example mIRC always interprets comma as argument seperator. To use comma properly inside the identifier, you'd need to escape it with $chr(44). This produces messy and sometimes even unreadable queries.
There's a simple solution for this, just pre-assign the query to a variable, and then pass the variable as the parameter to the identifier. You can either preassign with /set or /var, sometimes there might be problems with /var as it allows assignment of multiple local variables at same time, thus if mIRC sees a comma followed by a variable, it thinks you're declaring another variable.
The following code should clarify this:

Example 1 ; BAD! mIRC misinterprets the comma between col1 and col2 ; It is part of the query, but mIRC thinks it seperates args for $sqlite_query var %request = $sqlite_query(%db, SELECT col1, col2 FROM table) ; GOOD! No misinterpration this time var %sql = SELECT col1, col2 FROM table var %request = $sqlite_query(%db, %sql) Example 2 ; BAD! mIRC misinterprets the comma followed by %col2 ; It thinks you're declaring a list of local variables var %sql = INSERT INTO table (col1, col2) VALUES ( %var1 , %var2 ) ; GOOD! Unlike /var, /set only allows one variable at time, so the problem is fixed set %sql INSERT INTO table (col1, col2) VALUES ( %var1 , %var2 )

Keep in mind that /set sets a global variable instead of local variable. This should be fine if you use the same variable name (eg. %sql) everywhere, so your global variable list won't be polluted. You can also use /set -l to set a single local variable, without the danger of mIRC misinterpreting commas (thanks for the tip Chad!)
NOTE. When queries are executed as a command with /sqlite_exec, it isn't required to pre-assign the query, as none of the above problems are present.

Unlike most languages mIRC has no limitations for what characters, except for a space, a variable name can support. This means that whenever you write queries you need to make sure that variable names are detached from characters that are part of the SQL query, not the variable name.
For numeric types this usually is very easy, see example:

Example 1 ; BAD! mIRC doesn't treat %var1 as a variable because the word doesn't start with the % ; and mIRC interprets %var2 so that the closing paranthesis is part of it, which is wrong set %sql INSERT INTO table (col1, col2) VALUES (%var1, %var2) ; GOOD! Variable names are now seperated by spaces set %sql INSERT INTO table (col1, col2) VALUES ( %var1 , %var2 )

However, for string data this isn't quite as simple. The reason is because if you do '%var' mIRC doesn't recognize the variable because the word doesn't start with %, but in the other hand if you do ' %var ' the spaces will be includes in the string. You either need to connect the quotes to the variable with $+ or prequote the data with $sqlite_qt, as following:

Example 2 ; BAD! mIRC doesn't treat %str1 as a variable because the word doesn't start with the % ; For col2 the spaces will be included in the string, which isn't wanted either set %sql INSERT INTO table (col1, col2) VALUES ('%str1', ' %str2 ') ; GOOD! Now col1 and col2 both are quoted correctly set %sql INSERT INTO table (col1, col2) VALUES (' $+ %str1 $+ ', $+(',%str2,') ) ; GOOD! Prequoting the strings will fix the problem too var %str1 = $sqlite_qt(%str1), %str2 = $sqlite_qt(%str2) set %sql INSERT INTO table (col1, col2) VALUES ( %str1 , %str2 )

Whenever dealing with string data that might be unsafe it should be escaped properly before using it in a query. Unsafe string is a string that might contain a string delimiter (eg. a single quote) in it. The reason why it's unsafe is because when a single quote is seen in a wrong place, SQLite will misinterpret where the data ends, and thus start executing the query from wrong offset. This is especially dangerous with user input, as it allows SQL Injection. To prevent this scenario, always use $sqlite_escape_string on a potentially unsafe string.
Example of escaping user input proeprly:

Code ; BAD! Allows the user to input unescaped single quote, will break the query and allow injection ; Example of SQL injection: !login ' OR user = 'admin ; Will generate: SELECT * FROM users WHERE user = 'nick' AND pass = '' OR user = 'admin' on *:TEXT:!login *:?:{   var %user = $sqlite_qt($nick), %pass = $sqlite_qt($2-)   set %sql SELECT * FROM users WHERE user = %user AND pass = %pass   ; ... } ; GOOD! By escaping the password, SQL injection is no longer possible. ; Will generate: SELECT * FROM users WHERE user = 'nick' AND pass = ''' OR user = ''admin' ; In SQLite single quote is escaped by doubling it with another single quote on *:TEXT:!login *:?:{   var %user = $sqlite_qt($nick), %pass = $sqlite_qt($sqlite_escape_string($2-))   set %sql SELECT * FROM users WHERE user = %user AND pass = %pass   ; ... }

mIRC allows you to span a single command on multiple lines with the $& identifier. This can become very handy when writing long queries with tons of fields:

Code ; BAD! Too much text stuffed on single line sqlite_exec %db INSERT INTO table (one, two, three, four, five) VALUES ( %one , %two , %three , %four , %five ) ; GOOD! Looks nicer on multiple lines sqlite_exec %db $&   INSERT INTO table ( $&   one, two, three, four, five $&   ) VALUES ( $&   %one , %two , %three , %four , %five $&   )

Even though the guidelines explained in previous sections make writing queries cleaner and easier, there are times when some characters needs to be escaped in order for mIRC not to parse them. Here's a common example of such case:

Code ; BAD! mIRC interprets the closing %' as a variable, instead of a % followed by a quote var %sql = SELECT * FROM games WHERE title LIKE '% $+ %title $+ %' ; GOOD! This will make mIRC interpret % as a percentage character, not as a variable var %sql = SELECT * FROM games WHERE title LIKE '% $+ %title $+ % $+ '

Another way around this situation is to pre-format the LIKE clause:

Code ; GOOD! mIRC won't misinterpret percentage characters ; Note that we also pre-quote the data as explained before var %like = $sqlite_qt(% $+ %title $+ %) var %sql = SELECT * FROM games WHERE title LIKE %like