1. sqroot

    sqroot Plugin Developer

    With this guide I would first like to raise awareness in regards to SQL injections and then explain approaches to preventing SQL injections within Oxide.


    SQL injections
    An SQL injection is a type of attack used to attack databases.
    This attack is executed by making use of malicious user input and often allows clients to manipulate a database at will.

    Let's assume our table looks like this:
    Code (Text):
    +---+------+--------+
    |id | name | name2  |
    +---+------+--------+
    id is some sort of integer, name and name2 are strings.
    A possible query for this table might look like this:
    Code (Text):
    mysql.NewSql().Append("SELECT * FROM table WHERE name = \"" + arg[0] + "\" AND name2 = \"" + arg[1] + "\";")
    Assuming arg[0] and arg[1] are input strings provided by the user, this query is vulnerable to SQL injections.

    The first possible attack ends the query via a quote to inject custom SQL code after the input:
    Code (Text):
    arg[0] = "\"; DROP TABLE table;--"
    arg[1] = ""
    When combined with the SQL query, this results in the following query:
    Code (Text):
    SELECT * FROM table WHERE name = ""; DROP TABLE table;-- AND name2 = "";
    First a select is executed, then the table is dropped and the rest is commented out.

    The second possible attack ends the query via a quote to ignore the second condition name2 = "":
    Code (Text):
    arg[0] = "foo\";--"
    arg[1] = ""
    This results in the following query when combined:
    Code (Text):
    SELECT * FROM table WHERE name = "foo";-- AND name2 = "";
    The select is executed without considering the second condition because the second condition is commented out.

    The first attack is generally dangerous because you don't want your users to be able to delete your data, the second attack is dangerous in regards to bypassing authentication and leaking data.
    Let's assume name and name2 are username and password, then the attacker would be able to authenticate as any user without the password being considered.
    This might also result in the database leaking data when the result of the select can be accessed by the attacker, giving him access to data he shouldn't have access to.

    There are more possible attack vectors than this. You should always protect yourself against SQL injections when user input is involved.


    Preventing SQL injections in Oxide
    There are effectively two different types of mechanisms in Oxide that help with preventing SQL injections.
    The following applies to both the sqlite and the mysql extension, the only difference being that mysql is replaced with sqlite.

    The first one prevents multiple queries being executed in a single .Append() call, meaning the following code is invalid when added to the SQL query in a single call:
    Code (Text):
    SELECT * FROM table; DROP TABLE table;
    This automatically prevents the first kind of attack I mentioned, as the attacker cannot drop the table after ending the query with a quote.

    The second one prevents all kinds of attacks using parameterized queries.
    Parameterized queries are queries where user input is replaced with a placeholder. The SQL driver then parses the parameterized query, removes malicious code from user input and inserts the clean user input into the placeholders.
    In Oxide, our query from above can be written as a parameterized query as follows:
    Code (Text):
    mysql.NewSql().Append("SELECT * FROM table WHERE name = @0 AND name2 = @1;", arg[0], arg[1])
    @0 and @1 are the placeholders for the arguments provided in the varargs list after the query in our .Append() call.
    @0 refers to the first argument supplied, in this case arg[0], while @1 refers to the second argument supplied, in this case arg[1].
    Placeholders may not be enclosed by quotation marks.
    This parameterized query protects the database from the full spectrum of SQL injections.
    The actual C# syntax is very similar, just that you may use
    Code (Text):
    new Sql(query, args)
    instead of
    Code (Text):
    mysql.NewSql().Append(query, args)
    To sum it up, use parameterized queries whenever possible, even when input isn't derived from user input, as you might change that in the future and then forget that this kind of input isn't protected against SQL injections.
     
    Last edited: May 4, 2016
    Windamyre and Troubled like this.
  2. Alphawar

    Alphawar Plugin Developer

    @Wulf Is there any chance you could pin this so it stays up top or have this added somewhere so people dont miss this useful info???
     
  3. Netzulo

    Netzulo Naked Wanderer

  4. sqroot

    sqroot Plugin Developer

    This is wrong and a dangerous suggestion.
     
    Alphawar likes this.
  5. Netzulo

    Netzulo Naked Wanderer

    can you argument please ? maybe i can learn something today ? sorry if i wasnt' objective