Writing Queries

mIRC SQLite

Writing Queries
Because of the lack of quote delimited strings in mIRC you need to write your queries with care to ensure there aren't any problems. Here I will include few guidelines for writing queries in a clean, safely manner.
  • Pre-assign 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.

  • Use spaces and pre-quoting.
    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 )

  • Escape unsafe data.
    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
      ; ...
    }

  • Write long queries on multiple lines.
    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 $&
      )

  • Escape special characters when necessary.
    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

Escape Codes
The following table shows the special cases that needs to be escaped and the required code to escape them. Some characters have more than one way to escape them, they are all listed below each other:
Chr    Escape            
,      $chr(44)

%var   % $+ var
       $chr(37) $+ var

$id    $!id
       $ $+ id
       $chr(36) $+ id

[      $chr(91)


]      $chr(93)

{      $chr(123)

}      $chr(125)
The characters [ ] { and } only needs to be escaped when seperated by spaces.