data/database.lua

   1 -- Freeciv - Copyright (C) 2011 - The Freeciv Project
   2 --   This program is free software; you can redistribute it and/or modify
   3 --   it under the terms of the GNU General Public License as published by
   4 --   the Free Software Foundation; either version 2, or (at your option)
   5 --   any later version.
   6 --
   7 --   This program is distributed in the hope that it will be useful,
   8 --   but WITHOUT ANY WARRANTY; without even the implied warranty of
   9 --   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  10 --   GNU General Public License for more details.
  11
  12 -- This file is the Freeciv server`s interface to the database backend
  13 -- when authentication is enabled. See doc/README.fcdb.
  14
  15 local dbh = nil
  16
  17 -- Machinery for debug logging of options
  18 local seen_options
  19 local function options_init()
  20   seen_options = {}
  21 end
  22 local function option_log(name, val, is_sensitive, source)
  23   if not seen_options[name] then
  24     seen_options[name] = true
  25     if is_sensitive then
  26       log.debug('Database option \'%s\': %s', name, source)
  27     else
  28       log.debug('Database option \'%s\': %s: value \'%s\'', name, source, val)
  29     end
  30   end
  31 end
  32
  33 -- Get an option from configuration file, falling back to sensible
  34 -- defaults where they exist
  35 local function get_option(name, is_sensitive)
  36   local defaults = {
  37     backend    = "sqlite",
  38     table_user = "fcdb_auth",
  39     table_log  = "fcdb_log"
  40   }
  41   local val = fcdb.option(name)
  42   if val then
  43     option_log(name, val, is_sensitive, 'read from file')
  44   else
  45     val = defaults[name]
  46     if val then
  47       option_log(name, val, is_sensitive, 'using default')
  48     end
  49   end
  50   if not val then
  51     log.error('Database option \'%s\' not specified in configuration file',
  52               name)
  53   end
  54   return val
  55 end
  56
  57 -- connect to a MySQL database (or stop with an error)
  58 local function mysql_connect()
  59   local err -- error message
  60
  61   if dbh then
  62     dbh:close()
  63   end
  64
  65   local sql = luasql.mysql()
  66
  67   log.verbose('MySQL database version is %s.', luasql._MYSQLVERSION)
  68
  69   -- Load the database parameters.
  70   local database = get_option("database")
  71   local user     = get_option("user")
  72   local password = get_option("password", true)
  73   local host     = get_option("host")
  74   local port     = get_option("port")
  75
  76   dbh, err = sql:connect(database, user, password, host, port)
  77   if not dbh then
  78     log.error('[mysql:connect]: %s', err)
  79     return fcdb.status.ERROR
  80   else
  81     return fcdb.status.TRUE
  82   end
  83 end
  84
  85 -- open a SQLite database (or stop with an error)
  86 local function sqlite_connect()
  87   local err -- error message
  88
  89   if dbh then
  90     dbh:close()
  91   end
  92
  93   local sql = luasql.sqlite3()
  94
  95   -- Load the database parameters.
  96   local database = get_option("database")
  97
  98   dbh, err = sql:connect(database)
  99   if not dbh then
 100     log.error('[sqlite:connect]: %s', err)
 101     return fcdb.status.ERROR
 102   else
 103     return fcdb.status.TRUE
 104   end
 105 end
 106
 107 -- execute a sql query
 108 local function execute(query)
 109   local res -- result handle
 110   local err -- error message
 111
 112   if not dbh then
 113     return fcdb.status.ERROR, "[execute] Invalid database handle."
 114   end
 115
 116   -- log.verbose("Database query: %s", query)
 117
 118   res, err = dbh:execute(query)
 119   if not res then
 120     log.error("[luasql:execute]: %s", err)
 121     return fcdb.status.ERROR, err
 122   else
 123     return fcdb.status.TRUE, res
 124   end
 125 end
 126
 127 -- DIRTY: return a string to put in a database query which gets the
 128 -- current time (in seconds since the epoch, UTC).
 129 -- (This should be replaced with Lua os.time() once the script has access
 130 -- to this, see <http://gna.org/bugs/?19729>.)
 131 function sql_time()
 132   local backend = get_option("backend")
 133   if backend == 'mysql' then
 134     return 'UNIX_TIMESTAMP()'
 135   elseif backend == 'sqlite' then
 136     return 'strftime(\'%s\',\'now\')'
 137   else
 138     log.error('Don\'t know how to do timestamps for database backend \'%s\'', backend)
 139     return 'ERROR'
 140   end
 141 end
 142
 143 -- Set up tables for an SQLite database.
 144 -- (Since there`s no concept of user rights, we can do this directly from Lua,
 145 -- without needing a separate script like MySQL. The server operator can do
 146 -- "/fcdb lua sqlite_createdb()" from the server prompt.)
 147 function sqlite_createdb()
 148   local query
 149   local res
 150
 151   if get_option("backend") ~= 'sqlite' then
 152     log.error("'backend' in configuration file must be 'sqlite'")
 153     return fcdb.status.ERROR
 154   end
 155
 156   local table_user = get_option("table_user")
 157   local table_log  = get_option("table_log")
 158
 159   if not dbh then
 160     log.error("Missing database connection...")
 161     return fcdb.status.ERROR
 162   end
 163
 164   query = string.format([[
 165 CREATE TABLE %s (
 166   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 167   name VARCHAR(48) default NULL UNIQUE,
 168   password VARCHAR(32) default NULL,
 169   email VARCHAR default NULL,
 170   createtime INTEGER default NULL,
 171   accesstime INTEGER default NULL,
 172   address VARCHAR default NULL,
 173   createaddress VARCHAR default NULL,
 174   logincount INTEGER default '0'
 175 );
 176 ]], table_user)
 177   status, res = execute(query)
 178   if status == fcdb.status.TRUE then
 179     log.normal("Successfully created user table '%s'", table_user)
 180   else
 181     log.error("Error creating user table '%s'", table_user)
 182     return fcdb.status.ERROR
 183   end
 184
 185   query = string.format([[
 186 CREATE TABLE %s (
 187   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 188   name VARCHAR(48) default NULL,
 189   logintime INTEGER default NULL,
 190   address VARCHAR default NULL,
 191   succeed TEXT default 'S'
 192 );]], table_log)
 193   status, res = execute(query)
 194   if status == fcdb.status.TRUE then
 195     log.normal("Successfully created log table '%s'", table_log)
 196   else
 197     log.error("Error creating log table '%s'", table_log)
 198     return fcdb.status.ERROR
 199   end
 200
 201   return fcdb.status.TRUE
 202
 203 end
 204
 205 -- **************************************************************************
 206 -- For MySQL, the following shapes of tables are expected
 207 -- (scripts/setup_auth_server.sh automates this):
 208 --
 209 -- CREATE TABLE fcdb_auth (
 210 --   id int(11) NOT NULL auto_increment,
 211 --   name varchar(48) default NULL,
 212 --   password varchar(32) default NULL,
 213 --   email varchar(128) default NULL,
 214 --   createtime int(11) default NULL,
 215 --   accesstime int(11) default NULL,
 216 --   address varchar(255) default NULL,
 217 --   createaddress varchar(255) default NULL,
 218 --   logincount int(11) default '0',
 219 --   PRIMARY KEY  (id),
 220 --   UNIQUE KEY name (name)
 221 -- );
 222 --
 223 -- CREATE TABLE fcdb_log (
 224 --   id int(11) NOT NULL auto_increment,
 225 --   name varchar(48) default NULL,
 226 --   logintime int(11) default NULL,
 227 --   address varchar(255) default NULL,
 228 --   succeed enum('S','F') default 'S',
 229 --   PRIMARY KEY  (id)
 230 -- );
 231 --
 232 -- N.B. if the tables are not of this format, then the select, insert,
 233 --      and update syntax in the following functions must be changed.
 234 -- **************************************************************************
 235
 236 -- **************************************************************************
 237 -- freeciv user auth functions
 238 -- **************************************************************************
 239
 240 -- load user data
 241 function user_load(conn)
 242   local status  -- return value (status of the request)
 243   local res     -- result handle
 244   local row     -- one row of the sql result
 245   local query   -- sql query
 246
 247   local fields = 'password'
 248
 249   local table_user = get_option("table_user")
 250   local table_log  = get_option("table_log")
 251
 252   if not dbh then
 253     log.error("Missing database connection...")
 254     return fcdb.status.ERROR
 255   end
 256
 257   local username = dbh:escape(auth.get_username(conn))
 258   local ipaddr = dbh:escape(auth.get_ipaddr(conn))
 259
 260   -- get the password for this user
 261   query = string.format([[SELECT %s FROM %s WHERE name = '%s']],
 262                         fields, table_user, username)
 263   status, res = execute(query)
 264   if status ~= fcdb.status.TRUE then
 265     return fcdb.status.ERROR
 266   end
 267
 268   row = res:fetch({}, 'a')
 269   if not row then
 270     -- No match
 271     res:close()
 272     return fcdb.status.FALSE
 273   end
 274
 275   -- There should be only one result
 276   if res:fetch() then
 277     log.error('[user_load]: multiple entries (%d) for user: %s',
 278               numrows, username)
 279     res:close()
 280     return fcdb.status.FALSE
 281   end
 282
 283   auth.set_password(conn, row.password)
 284
 285   res:close()
 286
 287   return fcdb.status.TRUE
 288 end
 289
 290 -- save a user to the database
 291 function user_save(conn)
 292   local status  -- return value (status of the request)
 293   local res     -- result handle
 294   local query   -- sql query
 295
 296   local table_user = get_option("table_user")
 297
 298   if not dbh then
 299     log.error("Missing database connection...")
 300     return fcdb.status.ERROR
 301   end
 302
 303   local username = dbh:escape(auth.get_username(conn))
 304   local password = dbh:escape(auth.get_password(conn))
 305   local ipaddr = auth.get_ipaddr(conn)
 306
 307   -- insert the user
 308   --local now = os.time()
 309   query = string.format([[INSERT INTO %s VALUES (NULL, '%s', '%s',
 310                           NULL, %s, %s, '%s', '%s', 0)]],
 311                         table_user, username, password,
 312                         sql_time(), sql_time(),
 313                         ipaddr, ipaddr)
 314   status, res = execute(query)
 315   if status ~= fcdb.status.TRUE then
 316     return fcdb.status.ERROR
 317   end
 318
 319   -- log this session
 320   return user_log(conn, true)
 321 end
 322
 323 -- log the session
 324 function user_log(conn, success)
 325   local status  -- return value (status of the request)
 326   local res     -- result handle
 327   local query   -- sql query
 328
 329   if not dbh then
 330     log.error("Missing database connection...")
 331     return fcdb.status.ERROR
 332   end
 333
 334   local table_user = get_option("table_user")
 335   local table_log  = get_option("table_log")
 336
 337   local username = dbh:escape(auth.get_username(conn))
 338   local ipaddr = auth.get_ipaddr(conn)
 339   local success_str = success and 'S' or 'F'
 340
 341   -- update user data
 342   --local now = os.time()
 343   query = string.format([[UPDATE %s SET accesstime = %s, address = '%s',
 344                           logincount = logincount + 1
 345                           WHERE name = '%s']], table_user, sql_time(),
 346                           ipaddr, username)
 347   status, res = execute(query)
 348   if status ~= fcdb.status.TRUE then
 349     return fcdb.status.ERROR
 350   end
 351
 352   -- insert the log row for this user
 353   query = string.format([[INSERT INTO %s (name, logintime, address, succeed)
 354                           VALUES ('%s', %s, '%s', '%s')]],
 355                         table_log, username, sql_time(), ipaddr, success_str)
 356   status, res = execute(query)
 357   if status ~= fcdb.status.TRUE then
 358     return fcdb.status.ERROR
 359   end
 360
 361   return fcdb.status.TRUE
 362 end
 363
 364 -- **************************************************************************
 365 -- freeciv database entry functions
 366 -- **************************************************************************
 367
 368 -- test and initialise the database connection
 369 function database_init()
 370   local status -- return value (status of the request)
 371
 372   options_init()
 373
 374   local backend = get_option("backend")
 375   if backend == 'mysql' then
 376     log.verbose('Opening MySQL database connection...')
 377     status = mysql_connect()
 378   elseif backend == 'sqlite' then
 379     log.verbose('Opening SQLite database connection...')
 380     status = sqlite_connect()
 381   else
 382     log.error('Database backend \'%s\' not supported by database.lua', backend)
 383     return fcdb.status.ERROR
 384   end
 385
 386   if status == fcdb.status.TRUE then
 387     log.verbose('Database connection successful.')
 388   end
 389
 390   return status
 391 end
 392
 393 -- free the database connection
 394 function database_free()
 395   log.verbose('Closing database connection...')
 396
 397   if dbh then
 398     dbh:close()
 399   end
 400
 401   return fcdb.status.TRUE;
 402 end