HiveBrain v1.2.0
Get Started
← Back to all entries
patterncsharpMinor

Using OLEDB to connect to a MS Access 2007 file

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
2007fileconnectoledbusingaccess

Problem

I am working on a program which use OLEDB to connect to a MS Access 2007 file. My program has a possibility to add and delete records from file by using SQL statements which select deleted item by ID.

Now, I was warned that my code might cause SQL injection which would delete all entries from the file. How can I prevent this? Where lies the problem? One person mentioned that I could avoid it by having good field verification, but what should I verify for?

This is how I delete items:

// SQL query which will delete entry by using entry ID.
string SQL = "DELETE FROM PersonalData WHERE DataID = " + txtEntryID.Text;

private void DeleteData(string SQL)
{
    // Creating an object allowing me connecting to the database.
    // Using parameters in command will avoid attempts of SQL injection.
    OleDbConnection objOleDbConnection = new OleDbConnection();
    // Creating command object.
    objOleDbConnection.ConnectionString =
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + filePath + ";" +
        "Persist Security Info=False;" +
        "Jet OLEDB:Database Password=" + pass + ";";
    OleDbCommand objOleDbCommand = new OleDbCommand();

    objOleDbCommand.CommandText = SQL;

    // Assigning a connection string to the command.
    objOleDbCommand.Connection = objOleDbConnection;

    try
    {
        // Open database connection.
        objOleDbConnection.Open();
        objOleDbCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        // Displaying any errors that 
        // might have occured.
        MessageBox.Show("Error: " + ex.Message);
    }
    finally
    {
        // Close the database connection.
        objOleDbConnection.Close();
    }

    // Refreshing state of main window.
    mainWindow.DisplayFileContent(filePath);

    lblMessage.Text = "Data was successfully deleted.";

    // Clearing text box field.
    txtEntryID.Clear();
}

Solution

Unless I misunderstand, you are using unfiltered user input from txtEntryID.

Never trust user input.

What if the user fills txtEntryID with 123 OR 1=1?

The query will delete everything:

DELETE FROM PersonalData WHERE DataID = 123 OR 1=1


What if, then, the user inputs 123; UPDATE Account SET credit = 1000 WHERE userId = me:

DELETE FROM PersonalData WHERE DataID = 123; UPDATE Account SET credit = 1000 WHERE userId = me

Code Snippets

DELETE FROM PersonalData WHERE DataID = 123 OR 1=1
DELETE FROM PersonalData WHERE DataID = 123; UPDATE Account SET credit = 1000 WHERE userId = me

Context

StackExchange Code Review Q#6444, answer score: 8

Revisions (0)

No revisions yet.