C#•8mo ago

❔ Is this enough to clean a database entry?

I'm entering user-defined text into an SQL table but dont want my tables getting dropped. Does this work?

if (unsafeText.Contains('\'', '\"', '`'))
                {
                    //give error message
                    return;
                }

if (unsafeText.Contains('\'', '\"', '`'))
                {
                    //give error message
                    return;
                }

8 Replies

Pobiega•8mo ago

How are you doing the insert? most SQL clients have ways to parameterize the query, so you are safe from injection

joules•8mo ago

it's a text-based command through Microsoft.Data.Sqlite

Pobiega•8mo ago

so a SqlCommand?

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored
    // in an xml column.
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored
    // in an xml column.
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

joules•8mo ago

it's not cute

public static bool UpdateGuildStringField(ulong serverId, string fieldToUpdate, string newValue)
    {
        var sqliteCommand = ServerDataConnection.CreateCommand();
        try
        {
            sqliteCommand.CommandText =
                @$"UPDATE servers
                SET {fieldToUpdate} = '{newValue}'
                WHERE id is '{serverId}'";
            sqliteCommand.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

public static bool UpdateGuildStringField(ulong serverId, string fieldToUpdate, string newValue)
    {
        var sqliteCommand = ServerDataConnection.CreateCommand();
        try
        {
            sqliteCommand.CommandText =
                @$"UPDATE servers
                SET {fieldToUpdate} = '{newValue}'
                WHERE id is '{serverId}'";
            sqliteCommand.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

Pobiega•8mo ago

look at this example never ever use string interpolation or concats to make a query use a parameterized query

joules•8mo ago

i see

Angius•8mo ago

Parametrized queries are the one and only proper way to insert parameters into the query

Accord•8mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Is this enough to clean a database entry?