using System;

using System.IO;

using System.Net.Http;

using System.Net;

using System.Data.SQLite;

public class Program

{

    public static void Main()

    {

        DownloadDB();

        string pathToDBFile = @"Data Source=WebSecurity.db"; // <== Replace <PATH TO SQLITE DB FILE> to the path of the file on your computer

​

        SQLiteConnection conn = new SQLiteConnection(pathToDBFile);

​

        SusceptibleToSQLi(conn);

        //NotSusceptibleToSQLi(conn);

        //FixSQLi(conn);

    }

​

    public static void SusceptibleToSQLi(SQLiteConnection conn)

    {

        try

        {

            conn.Open();

        }

        catch

        {

            Console.WriteLine("Problem connecting to database file.");

        }

​

        SQLiteCommand cmd = conn.CreateCommand();

​

        string expectedInput = "CA"; // This input is the two letter state code and it an expected input from users of our WebApp

        string maliciousInput = "CA' UNION SELECT ingredients from SecretRecipes;--"; // This input is designed to perform SQL injection. Basically it takes the expected input and adds additional SQL code to get information from another table in our DB.

​

        string state = expectedInput;

        //state = maliciousInput; // <---- UNCOMMENT LINE TO PERFORM SQLi ATTACK using malicious input

​

        cmd.CommandText = "SELECT BrandName FROM Competition WHERE State='" + state + "'";

​

        SQLiteDataReader sqlDR = cmd.ExecuteReader();

​

        while (sqlDR.Read())

        {

            Console.WriteLine(sqlDR.GetString(0));

        }

​

        conn.Close();

    }

​

    public static void NotSusceptibleToSQLi(SQLiteConnection conn)

    {

        try

        {

            conn.Open();

        }

        catch

        {

            Console.WriteLine("Problem connecting to database file.");

        }

​

        SQLiteCommand cmd = conn.CreateCommand();

​

        string expectedInput = "CA"; // This input is the two letter state code and it an expected input from users of our WebApp

        string maliciousInput = "CA' UNION SELECT ingredients from SecretRecipes;--"; // This input is designed to perform SQL injection. Basically it takes the expected input and adds additional SQL code to get information from another table in our DB.

​

        string state = expectedInput;

        //state = maliciousInput; // <---- UNCOMMENT LINE TO PERFORM SQLi ATTACK using malicious input

​

        cmd.CommandText = "SELECT BrandName FROM Competition WHERE State=@state";

        cmd.Parameters.AddWithValue("@state", state);

​

        SQLiteDataReader sqlDR = cmd.ExecuteReader();

​

        while (sqlDR.Read())

        {

            Console.WriteLine(sqlDR.GetString(0));

        }

​

        conn.Close();

    }

​

    public static void FixSQLi(SQLiteConnection conn)

    {

        try

        {

            conn.Open();

        }

        catch

        {

            Console.WriteLine("Problem connecting to database file.");

        }

​

        SQLiteCommand cmd = conn.CreateCommand();

​

        string expectedDescription = "%" + "origin%"; // We want to search for descriptions that have origin in them (origin, original, originally, etc.). This is our wildcard string. The concatenation is there because percentage symbols need to be quoted or escaped in some 

        string expectedCaneSugar = "1"; // SQLite doesn't have true Booleans (just 1 or 0 ints), so this is looking for brands that use cane sugar as a sweetener

        string maliciousCaneSugar = "1 UNION SELECT ingredients from SecretRecipes;--"; // This input is designed to perform SQL injection. Basically it takes the expected cane sugar input and adds additional SQL code to get information from another table in our DB.

​

        string sugar = expectedCaneSugar;

        //sugar = maliciousCaneSugar; // <---- UNCOMMENT LINE TO PERFORM SQLi ATTACK using malicious input

        cmd.CommandText = "SELECT BrandName from Competition WHERE Description LIKE '" + expectedDescription + "' and CaneSugar=" + sugar;

​

        SQLiteDataReader sqlDR = cmd.ExecuteReader();

​

        while (sqlDR.Read())

        {

            Console.WriteLine(sqlDR.GetString(0));

        }

​

        conn.Close();

    }

    public static async void DownloadDB()

    {

        string requestUrl = @"https://byu.box.com/shared/static/z3bh6fzgcsb3sdcymftuastk2hmgma0u.db";

        using (WebClient client = new WebClient())

        {

            client.DownloadFile(requestUrl, "WebSecurity.db");  

        }

    }

}