SQL Prepared Statements ... in MUSHcode?!

Submitted by walker on Wed, 2008-06-25 23:02

Softcode is ugly. So is SQL. But when you try and combine them, that's a real recipe for disaster. There are so many things to watch for when bridging the gap between mushcode and the database. You get tired pretty quickly of all the sqlescape()s you have to do, of losing where you are in a query because of too much interpolated softcode, escaping all the commas so that the sql() function doesn't think you're passing too many arguments, etc etc etc. The list goes on and on, and I'm sure you can think of more problems to add.

This whole process has bugged enough people that it's started to become a fad to use a wrapper. That's what I'm here to describe and give you. With a proper wrapper, all your mushcode can stay mushcode, with no hint of SQL or sqlescape(), and all your SQL can stay SQL, with no hint of mushcode.

I've always been a fan of bind variables, which unfortunately do not exist in PennMUSH. My wrapper creates as nicely an imitation as it can.

What's a bind variable? It's when you create an SQL statement using question marks instead of data that might be dynamic. Here's an example from another language:

query = conn.bindquery("INSERT INTO definitions (term, definition) VALUES (?,?)");

query.execute("shoe", "An inanimate object, which nonetheless has a 'soul'.");

The equivalent mushcode:

> think sql(INSERT INTO definitions (term\, definition) VALUES
    ("[sqlescape(shoe)]"\,"[sqlescape(An inanimate object\, which nonetheless has a 'soul'.)]"))

Horrifying. The "Bind Variables" method is so much cleaner, and offers an additional method of making code easier: You don't need to know the SQL to use it. Once you've created a bind query, you can use it anytime, just passing it arguments.

So I've attempted to recreate the awesomeness of bind variables in mushcode. The SQL Wrapper can be found here:

http://walker.pennmush.org/code/sqlwrapper.txt - Yes, it's ugly code, but it's ugly so yours can be clean!

So how is it used? You can write an SQL query in one attribute, and remain ignorant of the SQL in mushcode. All of the SQL queries are saved in the SQL` attribute tree. SQL`<name>

Then, you can use one of three functions to work with that query. The most basic of this is named, simply enough, 'query':

u(query,<queryname>,[<arg0>,<arg1>,...]) - This will execute the query kept in SQL`<queryname>, with each of the ?s replaced with the arguments. (First ? is arg0, second ? is arg1, etc). The arguments are sqlescaped, so you do NOT need to use sqlescape() in your own mushcode!

Here's something using the above example:

> @create Dictionary
> @parent Dictionary = SQL Wrapper
> @power Dictionary = sql_ok
> &SQL`DEFINE Dictionary=INSERT INTO definitions (term, definition) VALUES (?,?)
> @set Dictionary = !no_command
> &CMD`DEFINE Dictionary=$+define *=*:@pemit %#=Word '%0' defined! [u(query,define,squish(%0),squish(%1))]
> +define shoe=An inanimate object, which nonetheless has a 'soul'.)

Tada! Much cleaner, don't you agree? Because there is no mushcode or sql parsing happening of its arguments, you can feel safe when accepting user input!

... But here we have a problem. A successful insert/replace/delete/etc query returns an empty string, and an unsuccessful one returns #-1. Both evaluate to a boolean false, making it slightly more annoying to use.

So, what can we do? Fairly simple. Most SQL libraries have the same problem, and provide two functions: 'query' returns query results, and 'update' returns number of rows affected, or a false value on failure. So we provide a wrapper: u(update,...) that returns a 1 on success, or the query's #-1 SQL ERROR: result on failure.

u(update,<queryname>,[<arg0>,...]) - Identical to query, but it will return a 1 if the query ever returns an empty string.

> think u(dictionary/update,define,shoulder,Something on which is placed a burden)
> think u(dictionary/update,define,shoe,Something that goes on feet)
#-1 SQL ERROR: Duplicate entry 'shoe' for key 1

And to change our cmd`define to use it:

> &CMD`DEFINE dictionary=$+define *=*:@pemit %#=if(setr(0,u(update,define,%0,%1)),Word '%0' defined!,Error: %q0)

So now we have clean, useful ways to both do a standard sql query, and an update query that we can easily check the result of.

Now - We have mapsql(), which is a very handy, mush-like way to deal with results from an SQL query. Unfortunately, mapsql() doesn't use bind variables either, so we had to make mapquery! Here's a brief intro:

u(mapquery,<[obj/]attr>,<queryname>,[<arg0>,...]) - will do what we need, with a default output separator of %r.

> &SQL`SEARCH Dictionary=SELECT term, definition FROM definitions WHERE term LIKE ? ORDER BY term
> &MAPLINE Dictionary = align(>10 40,%1,%2)
> think u(Dictionary/mapquery,mapline,search,shoe)
      shoe An inanimate object, which nonetheless
           has a 'soul'.

Now we make this into a command, adding a '%' (The SQL version of '*' for glob matching) to the end of it:

> &CMD`SEARCH dictionary=$+search *:@pemit %#=Definitions found:%r[u(mapquery,mapline,search,%0\%)]
> +define shopkeep = Somebody who wants money for their stuff.
> +search sho
Definitions found:      
      shoe An inanimate object, which nonetheless  
           has a 'soul'.
  shopkeep Somebody who wants money for their     
  shoulder Something on which is placed a burden.

So, tada! We now have 3 functions that we can use quite easily with bind variables: 'query', 'update', and 'mapquery'.

Here's the full documentation:

SQL`<name> - A query definition. The value of the attribute is an SQL query that matches one of two formats:
    1) Position-based bind variables: SQL queries that use a ? in place of non-hardcoded variables.
    2) Number-based bind variables: SQL queries that use ?<0> through ?<7> for bind variable position.

    SQL-Platform specific queries can be created by using SQL`name`<platform>. platform is the value of config(sql_platform). If it exists, it will be used instead of SQL`name.

    ?s can be prefixed by _ to tell the parser to turn MUSH-style globs into SQL-style globs, as per GLOB2LIKE below.

    ?s prefixed by a @ tell the parser that that argument is a space-separated list intended to be turned into an SQL Array. e.g, it turns '1 2 3' into '("1", "2", "3")'. (Remember, in SQL, "1" is the same as 1 when it works with numbers.) This is useful for "SELECT * FROM foo WHERE fooid IN @?" queries.

U(QUERY,<name>[:<rowsep>[:<colsep>]][,<args>]) - Perform a query that returns results, with result rows separated by <rowsep>, cols by <colsep> (Both a space by default). Args are mapped onto the SQL query in SQL`<name>, using either methods 1 or 2 above.

U(UPDATE,<name>[,<args>]) - Identical to u(query), but designed for queries that perform updates - If the result string is empty, u(update) assumes success, and returns 1.

U(MAPSQL,<attrname>[:<rowsep>[:<dofieldnames>]],<name>[,<args>]) - Performs an SQL query, mapping <args> to its bind variables, using either bind method. Result rows are passed to <attrname> as %1-%9, with row number as %0. (Much like mapsql()). <rowsep> defaults to %r, and <dofieldnames> makes the first call to <attrname> have a rownum of 0 and %1-%9 are the names of the columns.

One helper function:

U(GLOB2SQL) - This turns a Mush glob pattern (with *s and ?s) into a glob pattern fit for SQL: * -> "%" and "?" -> "_". This can be automatically applied to your bind variable if you prefix the ? with a _. (e.g: _?) - It still does an sqlescape(), so your query is safe when you use _?.

And here's the dictionary, if you'd like it:

@create Dictionary
@link Dictionary = #0
@parent Dictionary=#2414
@power Dictionary = SQL_OK
@describe Dictionary = A simple SQL-based Dictionary lies here. You can search for definitions by using "+search <term>", and you can add new ones by using "+define <term>=<definition>".
&CREATE_TABLE Dictionary=CREATE TABLE definitions (term VARCHAR(255) NOT NULL, PRIMARY KEY (term), definitions MEDIUMTEXT NOT NULL)
&CMD Dictionary=Command tree.
&CMD`DEFINE Dictionary=$+define *=*:@pemit %#=if(setr(0,u(update,define,squish(%0),squish(%1))),Word '[squish(%0)]' defined!,Error: %q0)
&CMD`SEARCH Dictionary=$+search *:@pemit %#=Definitions found:%r[u(mapquery,mapline,search,%0\%)]
&MAPLINE Dictionary=align(>10 40,%1,%2)
&SQL Dictionary=SQL Bind Query Tree
&SQL`DEFINE Dictionary=INSERT INTO definitions (term, definition) VALUES (?,?)
&SQL`SEARCH Dictionary=SELECT term, definition FROM definitions WHERE term LIKE ?

@@ If you want all +define commands to succeed, change "INSERT INTO" to "REPLACE INTO"