Built-in SQLite database support

Page: 1 2
Feb 26 2014, 7:18 pm
It's no more or less safe than savefiles, the same precautions should be observed with both.
Feb 27 2014, 3:54 am
In response to A.T.H.K
A.T.H.K wrote:
Lummox JR wrote:
We're not encrypting the database, so there won't be any password stuff built in. I'm given to understand this is possible with an extension, but we're not using it. Might be a possible refinement later.
So .. If I somehow got hold of a database from a BYOND game I could do something like...

So it rather involved being on the other side of this airtight hatchway?

That's not a problem with BYOND, that's a problem with poor security/practices on the part of the system administrator(s) for the game.
Feb 27 2014, 2:10 pm
Quick tutorial on usage:

var/database/query/q = new
var/database/db = new("mydb.db")

// Add text to a query (this also works during New())
// Question marks get replaced by your args, quoted or converted to binary blobs if necessary
q.Add("SELECT pet_name, species FROM pets WHERE owner = ?", key)

// Run the query -- the argument is optional in subsequent calls for this datum
q.Execute(db)

// Get results from the query
while(q.NextRow())
  var/row = q.GetRowData()   // proc name chosen to line up with Dantom.DB
  usr << "[row["pet_name"]] is \a [row["species"]]."

// You can call q.Add() to start a new query; Clear() is called automatically after the query has been run


A few notes:

- SQLite databases are relatively loose on types. Stick with FLOAT, INTEGER, TEXT, and BLOB (for icons) and you'll be fine.
- When you insert a row, the format is "INSERT INTO table (col1,col2,...) VALUES (val1,val2,...)". Naturally you should use question marks for the values.
- Using "[q]" in a string should give you the current query text. Not in the reference. Undocumented goodness!
- If you're going to be inserting several rows, I recommend running "BEGIN TRANSACTION" as a query first, then "END TRANSACTION" after to commit it.
- When the db is opened, it's put into write-ahead log mode to avoid too much disk access. You can change this with SQLite's commands if you want.
- The database is shared by different /database datums, if the filename is the same. This means if you do change the write-ahead log mode, you only have to do it once as long as any datum still has this database open.
- Don't try using the same file on multiple servers at the same time. A clown will die.
Feb 27 2014, 2:28 pm
Are question marked values properly parameterized, or just quoted/blobbed and worked into the string?

Could you expound a bit more on [q], perhaps with an example?
Feb 28 2014, 6:51 am
In response to Topkasa
Topkasa wrote:
Are question marked values properly parameterized, or just quoted/blobbed and worked into the string?

The latter. Using SQLite's binding routines would be doable, but I don't see it being any different as a practical matter. For the future, keeping a list of numeric/string/blob values would be perfectly feasible to implement as a behind-the-scenes change if need be.

Could you expound a bit more on [q], perhaps with an example?

usr << "[q]"   // print out current query text
Feb 28 2014, 8:10 am
I was thinking as a way to help guard against SQL Injection attacks when I mentioned parameterization, especially if someone gets the bright idea to use this to store user input.

So, if I understand [q] right.. 
q.Add("SELECT pet_name, species FROM pets WHERE owner = ?", "topkasa")
usr << "[q]"
would output "SELECT pet_name, species FROM pets WHERE owner = 'topkasa'"?
Feb 28 2014, 9:15 am
In response to Topkasa
Topkasa wrote:
I was thinking as a way to help guard against SQL Injection attacks when I mentioned parameterization, especially if someone gets the bright idea to use this to store user input.

Quoting the values should already protect against injection. Apostrophes get escaped. Numbers of course don't need to be quoted.

So, if I understand [q] right...
q.Add("SELECT pet_name, species FROM pets WHERE owner = ?", "topkasa")
> usr << "[q]"

would output "SELECT pet_name, species FROM pets WHERE owner = 'topkasa'"?

That's correct.
Feb 28 2014, 3:42 pm
In response to Lummox JR
- Using "[q]" in a string should give you the current query text. Not in the reference. Undocumented goodness!

Boo; document it. D:
Feb 28 2014, 6:35 pm (Edited on Feb 28 2014, 6:51 pm)
In response to Lummox JR

Could you expound a bit more on [q], perhaps with an example?

usr << "[q]"   // print out current query text

I passed over it so figured I would point out the obvious that I missed to others that may skip over too. q is the variable name of the /database/query object in lummox's example.
Page: 1 2