Apache Module mod_lua

Basic Configuration

The basic module loading directive is

LoadModule lua_module modules/mod_lua.so

mod_lua provides a handler named lua-script, which can be used with a SetHandler or AddHandler directive:

<Files "*.lua">
    SetHandler lua-script
</Files>

This will cause mod_lua to handle requests for files ending in .lua by invoking that file's handle function.

For more flexibility, see LuaMapHandler.

Writing Handlers

In the Apache HTTP Server API, the handler is a specific kind of hook responsible for generating the response. Examples of modules that include a handler are mod_proxy, mod_cgi, and mod_status.

mod_lua always looks to invoke a Lua function for the handler, rather than just evaluating a script body CGI style. A handler function looks something like this:

example.lua

-- example handler

require "string"

--[[
     This is the default method name for Lua handlers, see the optional
     function-name in the LuaMapHandler directive to choose a different
     entry point.
--]]
function handle(r)
    r.content_type = "text/plain"

    if r.method == 'GET' then
        r:puts("Hello Lua World!\n")
        for k, v in pairs( r:parseargs() ) do
            r:puts( string.format("%s: %s\n", k, v) )
        end
    elseif r.method == 'POST' then
        r:puts("Hello Lua World!\n")
        for k, v in pairs( r:parsebody() ) do
            r:puts( string.format("%s: %s\n", k, v) )
        end
    elseif r.method == 'PUT' then
-- use our own Error contents
        r:puts("Unsupported HTTP method " .. r.method)
        r.status = 405
        return apache2.OK
    else
-- use the ErrorDocument
        return 501
    end
    return apache2.OK
end

This handler function just prints out the uri or form encoded arguments to a plaintext page.

This means (and in fact encourages) that you can have multiple handlers (or hooks, or filters) in the same script.

Writing Authorization Providers

mod_authz_core provides a high-level interface to authorization that is much easier to use than using into the relevant hooks directly. The first argument to the Require directive gives the name of the responsible authorization provider. For any Require line, mod_authz_core will call the authorization provider of the given name, passing the rest of the line as parameters. The provider will then check authorization and pass the result as return value.

The authz provider is normally called before authentication. If it needs to know the authenticated user name (or if the user will be authenticated at all), the provider must return apache2.AUTHZ_DENIED_NO_USER. This will cause authentication to proceed and the authz provider to be called a second time.

The following authz provider function takes two arguments, one ip address and one user name. It will allow access from the given ip address without authentication, or if the authenticated user matches the second argument:

authz_provider.lua


require 'apache2'

function authz_check_foo(r, ip, user)
    if r.useragent_ip == ip then
        return apache2.AUTHZ_GRANTED
    elseif r.user == nil then
        return apache2.AUTHZ_DENIED_NO_USER
    elseif r.user == user then
        return apache2.AUTHZ_GRANTED
    else
        return apache2.AUTHZ_DENIED
    end
end

The following configuration registers this function as provider foo and configures it for URL /:

LuaAuthzProvider foo authz_provider.lua authz_check_foo
<Location "/">
  Require foo 10.1.2.3 john_doe
</Location>

Writing Hooks

Hook functions are how modules (and Lua scripts) participate in the processing of requests. Each type of hook exposed by the server exists for a specific purpose, such as mapping requests to the file system, performing access control, or setting mime types:

Hook functions are passed the request object as their only argument (except for LuaAuthzProvider, which also gets passed the arguments from the Require directive). They can return any value, depending on the hook, but most commonly they'll return OK, DONE, or DECLINED, which you can write in Lua as apache2.OK, apache2.DONE, or apache2.DECLINED, or else an HTTP status code.

translate_name.lua

-- example hook that rewrites the URI to a filesystem path.

require 'apache2'

function translate_name(r)
    if r.uri == "/translate-name" then
        r.filename = r.document_root .. "/find_me.txt"
        return apache2.OK
    end
    -- we don't care about this URL, give another module a chance
    return apache2.DECLINED
end

translate_name2.lua

--[[ example hook that rewrites one URI to another URI. It returns a
     apache2.DECLINED to give other URL mappers a chance to work on the
     substitution, including the core translate_name hook which maps based
     on the DocumentRoot.

     Note: Use the early/late flags in the directive to make it run before
           or after mod_alias.
--]]

require 'apache2'

function translate_name(r)
    if r.uri == "/translate-name" then
        r.uri = "/find_me.txt"
        return apache2.DECLINED
    end
    return apache2.DECLINED
end

Data Structures

request_rec

The request_rec is mapped in as a userdata. It has a metatable which lets you do useful things with it. For the most part it has the same fields as the request_rec struct, many of which are writable as well as readable. (The table fields' content can be changed, but the fields themselves cannot be set to different tables.)

Name	Lua type	Writable	Description
allowoverrides	string	no	The AllowOverride options applied to the current request.
ap_auth_type	string	no	If an authentication check was made, this is set to the type of authentication (f.x. basic)
args	string	yes	The query string arguments extracted from the request (f.x. foo=bar&name=johnsmith)
assbackwards	boolean	no	Set to true if this is an HTTP/0.9 style request (e.g. GET /foo (with no headers) )
auth_name	string	no	The realm name used for authorization (if applicable).
banner	string	no	The server banner, f.x. Apache HTTP Server/2.4.3 openssl/0.9.8c
basic_auth_pw	string	no	The basic auth password sent with this request, if any
canonical_filename	string	no	The canonical filename of the request
content_encoding	string	no	The content encoding of the current request
content_type	string	yes	The content type of the current request, as determined in the type_check phase (f.x. image/gif or text/html)
context_prefix	string	no
context_document_root	string	no
document_root	string	no	The document root of the host
err_headers_out	table	no	MIME header environment for the response, printed even on errors and persist across internal redirects
filename	string	yes	The file name that the request maps to, f.x. /www/example.com/foo.txt. This can be changed in the translate-name or map-to-storage phases of a request to allow the default handler (or script handlers) to serve a different file than what was requested.
handler	string	yes	The name of the handler that should serve this request, f.x. lua-script if it is to be served by mod_lua. This is typically set by the AddHandler or SetHandler directives, but could also be set via mod_lua to allow another handler to serve up a specific request that would otherwise not be served by it.
headers_in	table	yes	MIME header environment from the request. This contains headers such as Host, User-Agent, Referer and so on.
headers_out	table	yes	MIME header environment for the response.
hostname	string	no	The host name, as set by the Host: header or by a full URI.
is_https	boolean	no	Whether or not this request is done via HTTPS
is_initial_req	boolean	no	Whether this request is the initial request or a sub-request
limit_req_body	number	no	The size limit of the request body for this request, or 0 if no limit.
log_id	string	no	The ID to identify request in access and error log.
method	string	no	The request method, f.x. GET or POST.
notes	table	yes	A list of notes that can be passed on from one module to another.
options	string	no	The Options directive applied to the current request.
path_info	string	no	The PATH_INFO extracted from this request.
port	number	no	The server port used by the request.
protocol	string	no	The protocol used, f.x. HTTP/1.1
proxyreq	string	yes	Denotes whether this is a proxy request or not. This value is generally set in the post_read_request/translate_name phase of a request.
range	string	no	The contents of the Range: header.
remaining	number	no	The number of bytes remaining to be read from the request body.
server_built	string	no	The time the server executable was built.
server_name	string	no	The server name for this request.
some_auth_required	boolean	no	Whether some authorization is/was required for this request.
subprocess_env	table	yes	The environment variables set for this request.
started	number	no	The time the server was (re)started, in seconds since the epoch (Jan 1st, 1970)
status	number	yes	The (current) HTTP return code for this request, f.x. 200 or 404.
the_request	string	no	The request string as sent by the client, f.x. GET /foo/bar HTTP/1.1.
unparsed_uri	string	no	The unparsed URI of the request
uri	string	yes	The URI after it has been parsed by httpd
user	string	yes	If an authentication check has been made, this is set to the name of the authenticated user.
useragent_ip	string	no	The IP of the user agent making the request

Built in functions

The request_rec object has (at least) the following methods:

r:flush()   -- flushes the output buffer.
            -- Returns true if the flush was successful, false otherwise.

while we_have_stuff_to_send do
    r:puts("Bla bla bla\n") -- print something to client
    r:flush() -- flush the buffer (send to client)
    r.usleep(500000) -- fake processing time for 0.5 sec. and repeat
end

r:addoutputfilter(name|function) -- add an output filter:

r:addoutputfilter("fooFilter") -- add the fooFilter to the output stream

r:sendfile(filename) -- sends an entire file to the client, using sendfile if supported by the current platform:

if use_sendfile_thing then
    r:sendfile("/var/www/large_file.img")
end

r:parseargs() -- returns two tables; one standard key/value table for regular GET data, 
              -- and one for multi-value data (fx. foo=1&foo=2&foo=3):

local GET, GETMULTI = r:parseargs()
r:puts("Your name is: " .. GET['name'] or "Unknown")

r:parsebody([sizeLimit]) -- parse the request body as a POST and return two lua tables,
                         -- just like r:parseargs().
                         -- An optional number may be passed to specify the maximum number 
                         -- of bytes to parse. Default is 8192 bytes:
                 
local POST, POSTMULTI = r:parsebody(1024*1024)
r:puts("Your name is: " .. POST['name'] or "Unknown")

r:puts("hello", " world", "!") -- print to response body, self explanatory

r:write("a single string") -- print to response body, self explanatory

r:escape_html("<html>test</html>") -- Escapes HTML code and returns the escaped result

r:base64_encode(string) -- Encodes a string using the Base64 encoding standard:

local encoded = r:base64_encode("This is a test") -- returns VGhpcyBpcyBhIHRlc3Q=

r:base64_decode(string) -- Decodes a Base64-encoded string:

local decoded = r:base64_decode("VGhpcyBpcyBhIHRlc3Q=") -- returns 'This is a test'

r:md5(string) -- Calculates and returns the MD5 digest of a string (binary safe):

local hash = r:md5("This is a test") -- returns ce114e4501d2f4e2dcea3e17b546f339

r:sha1(string) -- Calculates and returns the SHA1 digest of a string (binary safe):

local hash = r:sha1("This is a test") -- returns a54d88e06612d820bc3be72877c74f257b561b19

r:escape(string) -- URL-Escapes a string:

local url = "http://foo.bar/1 2 3 & 4 + 5"
local escaped = r:escape(url) -- returns 'http%3a%2f%2ffoo.bar%2f1+2+3+%26+4+%2b+5'

r:unescape(string) -- Unescapes an URL-escaped string:

local url = "http%3a%2f%2ffoo.bar%2f1+2+3+%26+4+%2b+5"
local unescaped = r:unescape(url) -- returns 'http://foo.bar/1 2 3 & 4 + 5'

r:construct_url(string) -- Constructs an URL from an URI

local url = r:construct_url(r.uri)

r.mpm_query(number) -- Queries the server for MPM information using ap_mpm_query:

local mpm = r.mpm_query(14)
if mpm == 1 then
    r:puts("This server uses the Event MPM")
end

r:expr(string) -- Evaluates an expr string.

if r:expr("%{HTTP_HOST} =~ /^www/") then
    r:puts("This host name starts with www")
end

r:scoreboard_process(a) -- Queries the server for information about the process at position a:

local process = r:scoreboard_process(1)
r:puts("Server 1 has PID " .. process.pid)

r:scoreboard_worker(a, b) -- Queries for information about the worker thread, b, in process a:

local thread = r:scoreboard_worker(1, 1)
r:puts("Server 1's thread 1 has thread ID " .. thread.tid .. " and is in " .. thread.status .. " status")

r:clock() -- Returns the current time with microsecond precision

r:requestbody(filename) -- Reads and returns the request body of a request.
                -- If 'filename' is specified, it instead saves the
                -- contents to that file:
                
local input = r:requestbody()
r:puts("You sent the following request body to me:\n")
r:puts(input)

r:add_input_filter(filter_name) -- Adds 'filter_name' as an input filter

r.module_info(module_name) -- Queries the server for information about a module

local mod = r.module_info("mod_lua.c")
if mod then
    for k, v in pairs(mod.commands) do
       r:puts( ("%s: %s\n"):format(k,v)) -- print out all directives accepted by this module
    end
end

r:loaded_modules() -- Returns a list of modules loaded by httpd:

for k, module in pairs(r:loaded_modules()) do
    r:puts("I have loaded module " .. module .. "\n")
end

r:runtime_dir_relative(filename) -- Compute the name of a run-time file (e.g., shared memory "file") 
                         -- relative to the appropriate run-time directory.

r:server_info() -- Returns a table containing server information, such as 
                -- the name of the httpd executable file, mpm used etc.

r:set_document_root(file_path) -- Sets the document root for the request to file_path

r:set_context_info(prefix, docroot) -- Sets the context prefix and context document root for a request

r:os_escape_path(file_path) -- Converts an OS path to a URL in an OS dependent way

r:escape_logitem(string) -- Escapes a string for logging

r.strcmp_match(string, pattern) -- Checks if 'string' matches 'pattern' using strcmp_match (globs).
                        -- fx. whether 'www.example.com' matches '*.example.com':
                        
local match = r.strcmp_match("foobar.com", "foo*.com")
if match then 
    r:puts("foobar.com matches foo*.com")
end

r:set_keepalive() -- Sets the keepalive status for a request. Returns true if possible, false otherwise.

r:make_etag() -- Constructs and returns the etag for the current request.

r:send_interim_response(clear) -- Sends an interim (1xx) response to the client.
                       -- if 'clear' is true, available headers will be sent and cleared.

r:custom_response(status_code, string) -- Construct and set a custom response for a given status code.
                               -- This works much like the ErrorDocument directive:
                               
r:custom_response(404, "Baleted!")

r.exists_config_define(string) -- Checks whether a configuration definition exists or not:

if r.exists_config_define("FOO") then
    r:puts("httpd was probably run with -DFOO, or it was defined in the configuration")
end

r:state_query(string) -- Queries the server for state information

r:stat(filename [,wanted]) -- Runs stat() on a file, and returns a table with file information:

local info = r:stat("/var/www/foo.txt")
if info then
    r:puts("This file exists and was last modified at: " .. info.modified)
end

r:regex(string, pattern [,flags]) -- Runs a regular expression match on a string, returning captures if matched:

local matches = r:regex("foo bar baz", [[foo (\w+) (\S*)]])
if matches then
    r:puts("The regex matched, and the last word captured ($2) was: " .. matches[2])
end

-- Example ignoring case sensitivity:
local matches = r:regex("FOO bar BAz", [[(foo) bar]], 1)

-- Flags can be a bitwise combination of:
-- 0x01: Ignore case
-- 0x02: Multiline search

r.usleep(number_of_microseconds) -- Puts the script to sleep for a given number of microseconds.

r:dbacquire(dbType[, dbParams]) -- Acquires a connection to a database and returns a database class.
                        -- See 'Database connectivity' for details.

r:ivm_set("key", value) -- Set an Inter-VM variable to hold a specific value.
                        -- These values persist even though the VM is gone or not being used,
                        -- and so should only be used if MaxConnectionsPerChild is > 0
                        -- Values can be numbers, strings and booleans, and are stored on a 
                        -- per process basis (so they won't do much good with a prefork mpm)
                        
r:ivm_get("key")        -- Fetches a variable set by ivm_set. Returns the contents of the variable
                        -- if it exists or nil if no such variable exists.
                        
-- An example getter/setter that saves a global variable outside the VM:
function handle(r)
    -- First VM to call this will get no value, and will have to create it
    local foo = r:ivm_get("cached_data")
    if not foo then
        foo = do_some_calcs() -- fake some return value
        r:ivm_set("cached_data", foo) -- set it globally
    end
    r:puts("Cached data is: ", foo)
end

r:htpassword(string [,algorithm [,cost]]) -- Creates a password hash from a string.
                                          -- algorithm: 0 = APMD5 (default), 1 = SHA, 2 = BCRYPT, 3 = CRYPT.
                                          -- cost: only valid with BCRYPT algorithm (default = 5).

r:mkdir(dir [,mode]) -- Creates a directory and sets mode to optional mode parameter.

r:mkrdir(dir [,mode]) -- Creates directories recursive and sets mode to optional mode parameter.

r:rmdir(dir) -- Removes a directory.

r:touch(file [,mtime]) -- Sets the file modification time to current time or to optional mtime msec value.

r:get_direntries(dir) -- Returns a table with all directory entries.

function handle(r)
  local dir = r.context_document_root
  for _, f in ipairs(r:get_direntries(dir)) do
    local info = r:stat(dir .. "/" .. f)
    if info then
      local mtime = os.date(fmt, info.mtime / 1000000)
      local ftype = (info.filetype == 2) and "[dir] " or "[file]"
      r:puts( ("%s %s %10i %s\n"):format(ftype, mtime, info.size, f) )
    end
  end
end

r.date_parse_rfc(string) -- Parses a date/time string and returns seconds since epoche.

r:getcookie(key) -- Gets a HTTP cookie

r:setcookie{
  key = [key],
  value = [value],
  expires = [expiry],
  secure = [boolean],
  httponly = [boolean],
  path = [path],
  domain = [domain]
} -- Sets a HTTP cookie, for instance:

r:setcookie{
  key = "cookie1",
  value = "HDHfa9eyffh396rt",
  expires = os.time() + 86400,
  secure = true
}

r:wsupgrade() -- Upgrades a connection to WebSockets if possible (and requested):
if r:wsupgrade() then -- if we can upgrade:
    r:wswrite("Welcome to websockets!") -- write something to the client
    r:wsclose()  -- goodbye!
end

r:wsread() -- Reads a WebSocket frame from a WebSocket upgraded connection (see above):

local line, isFinal = r:wsread() -- isFinal denotes whether this is the final frame.
                                 -- If it isn't, then more frames can be read
r:wswrite("You wrote: " .. line)

r:wswrite(line) -- Writes a frame to a WebSocket client:
r:wswrite("Hello, world!")

r:wsclose() -- Closes a WebSocket request and terminates it for httpd:

if r:wsupgrade() then
    r:wswrite("Write something: ")
    local line = r:wsread() or "nothing"
    r:wswrite("You wrote: " .. line);
    r:wswrite("Goodbye!")
    r:wsclose()
end

Logging Functions

-- examples of logging messages
r:trace1("This is a trace log message") -- trace1 through trace8 can be used
r:debug("This is a debug log message")
r:info("This is an info log message")
r:notice("This is a notice log message")
r:warn("This is a warn log message")
r:err("This is an err log message")
r:alert("This is an alert log message")
r:crit("This is a crit log message")
r:emerg("This is an emerg log message")

apache2 Package

A package named apache2 is available with (at least) the following contents.

apache2.OK

internal constant OK. Handlers should return this if they've handled the request.

apache2.DECLINED

internal constant DECLINED. Handlers should return this if they are not going to handle the request.

apache2.DONE

internal constant DONE.

apache2.version

Apache HTTP server version string

apache2.HTTP_MOVED_TEMPORARILY

HTTP status code

apache2.PROXYREQ_NONE, apache2.PROXYREQ_PROXY, apache2.PROXYREQ_REVERSE, apache2.PROXYREQ_RESPONSE

internal constants used by mod_proxy

apache2.AUTHZ_DENIED, apache2.AUTHZ_GRANTED, apache2.AUTHZ_NEUTRAL, apache2.AUTHZ_GENERAL_ERROR, apache2.AUTHZ_DENIED_NO_USER

internal constants used by mod_authz_core

(Other HTTP status codes are not yet implemented.)

Modifying contents with Lua filters

Filter functions implemented via LuaInputFilter or LuaOutputFilter are designed as three-stage non-blocking functions using coroutines to suspend and resume a function as buckets are sent down the filter chain. The core structure of such a function is:

function filter(r)
    -- Our first yield is to signal that we are ready to receive buckets.
    -- Before this yield, we can set up our environment, check for conditions,
    -- and, if we deem it necessary, decline filtering a request alltogether:
    if something_bad then
        return -- This would skip this filter.
    end
    -- Regardless of whether we have data to prepend, a yield MUST be called here.
    -- Note that only output filters can prepend data. Input filters must use the 
    -- final stage to append data to the content.
    coroutine.yield([optional header to be prepended to the content])
    
    -- After we have yielded, buckets will be sent to us, one by one, and we can 
    -- do whatever we want with them and then pass on the result.
    -- Buckets are stored in the global variable 'bucket', so we create a loop
    -- that checks if 'bucket' is not nil:
    while bucket ~= nil do
        local output = mangle(bucket) -- Do some stuff to the content
        coroutine.yield(output) -- Return our new content to the filter chain
    end

    -- Once the buckets are gone, 'bucket' is set to nil, which will exit the 
    -- loop and land us here. Anything extra we want to append to the content
    -- can be done by doing a final yield here. Both input and output filters 
    -- can append data to the content in this phase.
    coroutine.yield([optional footer to be appended to the content])
end

Database connectivity

Mod_lua implements a simple database feature for querying and running commands on the most popular database engines (mySQL, PostgreSQL, FreeTDS, ODBC, SQLite, Oracle) as well as mod_dbd.

The example below shows how to acquire a database handle and return information from a table:

function handle(r)
    -- Acquire a database handle
    local database, err = r:dbacquire("mysql", "server=localhost,user=someuser,pass=somepass,dbname=mydb")
    if not err then
        -- Select some information from it
        local results, err = database:select(r, "SELECT `name`, `age` FROM `people` WHERE 1")
        if not err then
            local rows = results(0) -- fetch all rows synchronously
            for k, row in pairs(rows) do
                r:puts( string.format("Name: %s, Age: %s<br/>", row[1], row[2]) )
            end
        else
            r:puts("Database query error: " .. err)
        end
        database:close()
    else
        r:puts("Could not connect to the database: " .. err)
    end
end

To utilize mod_dbd, specify mod_dbd as the database type, or leave the field blank:

local database = r:dbacquire("mod_dbd")

Database object and contained functions

The database object returned by dbacquire has the following methods:

Normal select and query from a database:

-- Run a statement and return the number of rows affected:
local affected, errmsg = database:query(r, "DELETE FROM `tbl` WHERE 1")

-- Run a statement and return a result set that can be used synchronously or async:
local result, errmsg = database:select(r, "SELECT * FROM `people` WHERE 1")

Using prepared statements (recommended):

-- Create and run a prepared statement:
local statement, errmsg = database:prepare(r, "DELETE FROM `tbl` WHERE `age` > %u")
if not errmsg then
    local result, errmsg = statement:query(20) -- run the statement with age > 20
end

-- Fetch a prepared statement from a DBDPrepareSQL directive:
local statement, errmsg = database:prepared(r, "someTag")
if not errmsg then
    local result, errmsg = statement:select("John Doe", 123) -- inject the values "John Doe" and 123 into the statement
end

Escaping values, closing databases etc:

-- Escape a value for use in a statement:
local escaped = database:escape(r, [["'|blabla]])

-- Close a database connection and free up handles:
database:close()

-- Check whether a database connection is up and running:
local connected = database:active()

Working with result sets

The result set returned by db:select or by the prepared statement functions created through db:prepare can be used to fetch rows synchronously or asynchronously, depending on the row number specified:
result(0) fetches all rows in a synchronous manner, returning a table of rows.
result(-1) fetches the next available row in the set, asynchronously.
result(N) fetches row number N, asynchronously:

-- fetch a result set using a regular query:
local result, err = db:select(r, "SELECT * FROM `tbl` WHERE 1")

local rows = result(0) -- Fetch ALL rows synchronously
local row = result(-1) -- Fetch the next available row, asynchronously
local row = result(1234) -- Fetch row number 1234, asynchronously
local row = result(-1, true) -- Fetch the next available row, using row names as key indexes.

One can construct a function that returns an iterative function to iterate over all rows in a synchronous or asynchronous way, depending on the async argument:

function rows(resultset, async)
    local a = 0
    local function getnext()
        a = a + 1
        local row = resultset(-1)
        return row and a or nil, row
    end
    if not async then
        return pairs(resultset(0))
    else
        return getnext, self
    end
end

local statement, err = db:prepare(r, "SELECT * FROM `tbl` WHERE `age` > %u")
if not err then
     -- fetch rows asynchronously:
    local result, err = statement:select(20)
    if not err then
        for index, row in rows(result, true) do
            ....
        end
    end

     -- fetch rows synchronously:
    local result, err = statement:select(20)
    if not err then
        for index, row in rows(result, false) do
            ....
        end
    end
end

Closing a database connection

Database handles should be closed using database:close() when they are no longer needed. If you do not close them manually, they will eventually be garbage collected and closed by mod_lua, but you may end up having too many unused connections to the database if you leave the closing up to mod_lua. Essentially, the following two measures are the same:

-- Method 1: Manually close a handle
local database = r:dbacquire("mod_dbd")
database:close() -- All done

-- Method 2: Letting the garbage collector close it
local database = r:dbacquire("mod_dbd")
database = nil -- throw away the reference
collectgarbage() -- close the handle via GC

Precautions when working with databases

Although the standard query and run functions are freely available, it is recommended that you use prepared statements whenever possible, to both optimize performance (if your db handle lives on for a long time) and to minimize the risk of SQL injection attacks. run and query should only be used when there are no variables inserted into a statement (a static statement). When using dynamic statements, use db:prepare or db:prepared.