Table of Contents

Database

30 April 2017

  • Release 9.163 - add prepared statements support.
database (Commands)
TypeCorrade progressive command
Commanddatabase
DescriptionCorrade bundles an integrated SQLite based database. You can send SQL commands to Corrade and the database will be updated based on those commands.
Permissionsdatabase
Parametersgroup, password, SQL
Last ChangesRelease 9.163 - add prepared statements support.

The Corrade database command allows for prepared statements - use them at all costs and never use plain SQL queries if you do not want your database to be abused by well-chosen user-input.

Corrade bundles an integrated SQLite database per group that corresponds to the database configured in Corrade's configuration file Corrade.ini for each group.

Command Required Parameters Required Corrade Permissions Example
database group, password, SQL database
llInstantMessage(CORRADE,
    wasKeyValueEncode(
        [
            "command", "database",
            "group", wasURLEscape(GROUP),
            "password", wasURLEscape(PASSWORD),
            "SQL", wasURLEscape("SELECT * FROM highscores"),
            "callback", wasURLEscape(URL)
        ]
    )
);

When selecting from a database, Corrade will serialise the rows and columns as a pair of column name by column value.

Short Tutorial

Each group can have an SQLite database that can be handled from inside a grid or processed through external scripts. After you configure the per-group database file, you would need to create a table (or several tables). Let's assume you wanted to store some high-scores for various people. You would then create a new table for the group:

llRegionSayTo(CORRADE, 0,
    wasKeyValueEncode(
        [
            "command", "database",
            "group", wasURLEscape(GROUP),
            "password", wasURLEscape(PASSWORD),
            "SQL", wasURLEscape("CREATE TABLE highscores (name varchar(20), score int)"),
            "callback", wasURLEscape(URL) 
        ]
    )
);

Any errors will be posted to the callback along with the success status of executing the SQL statement. Now that we have created a table, let us insert a score - using prepared statements:

llRegionSayTo(CORRADE, 0,
    wasKeyValueEncode(
        [
            "command", "database",
            "group", wasURLEscape(GROUP),
            "password", wasURLEscape(PASSWORD),
            "SQL", wasURLEscape("INSERT INTO highscores (name, score) VALUES (:name, :score)"),
            "data", wasListToCSV(
                [
                    "name", "Me",
                    "score", "9001"
                ]
            ),
            "callback", wasURLEscape(URL) 
        ]
    )
);

:name and :score are parameter bindings and they will be substituted by the value of name, respectively score in the data CSV list. The parameters could have been concatenated directly into the string passed to the “SQL” key - however, that would have left the database wide open to SQL injections.

After that, we can select from the database, for example, to retrieve all the scores, we would issue:

llRegionSayTo(CORRADE, 0,
    wasKeyValueEncode(
        [
            "command", "database",
            "group", wasURLEscape(GROUP),
            "password", wasURLEscape(PASSWORD),
            "SQL", wasURLEscape("SELECT * FROM highscores"),
            "callback", wasURLEscape(URL) 
        ]
    )
);

at which point Corrade will answer by sending the data key to the callback: data=name,Me,score,9001.

It is also straightforward to query the highscores table in order to retrieve the score of Me:

llRegionSayTo(CORRADE, 0,
    wasKeyValueEncode(
        [
            "command", "database",
            "group", wasURLEscape(GROUP),
            "password", wasURLEscape(PASSWORD),
            "SQL", wasURLEscape("SELECT score FROM highscores WHERE name=:name"),
            "data", wasListToCSV(
                [
                    "name",
                    "Me"
                ]
            ),
            "callback", wasURLEscape(URL) 
        ]
    )
);

but remember that if instead of Me, you have a string that could conflict with the key-value data syntax, then you would have to escape the string using wasURLEscape.

Notes

  • Always use prepared statements by using parameter binding and passing the parameters through the data key when user-input is involved.

secondlife/scripted_agents/corrade/api/progressive/commands/database.txt · Last modified: 2017/04/30 22:52 by Wizardry and Steamworks

Access website using Tor


For the copyright, license, warranty and privacy terms for the usage of this website please see the license and privacy pages.