Unclosed quotation mark after the character string

Question

I'm developing a password storage application where a user has to enter a file name, choose a file extension from a combobox and then type their file content in the provided textbox. The filename and extension is only for if they choose to export it. Most of the data I'm inserting into the database is working fine and inserting/able to retrieve from SQL Server database without an issue. but as soon as my file content has characters such as ""/ '/ ()/ etc it gives me the error

System.Data.SqlClient.SqlException: 'Incorrect syntax near 't'. Unclosed quotation mark after the character string ')'.'

How can I make it possible for the file content string to be inserted into database exactly as entered into textbox: here's the code for the insert after clicking savefile button where I get the initial error:

private void btnSaveFile_Click(object sender, EventArgs e)
{
    string extensionType = comboExtension.GetItemText(comboExtension.SelectedItem);

    if (txtFileName.Text == "")
    {
        MessageBox.Show("Please Enter A Valid File Name", "Please Fill In", MessageBoxButtons.OK, MessageBoxIcon.Information);

    }
    else if (!txtFileName.Text.All(char.IsLetterOrDigit))
    {
        MessageBox.Show("File Name can only contain letters or numbers, No Special Characters", "Incorrect Input", MessageBoxButtons.OK, MessageBoxIcon.Error);
        txtFileName.Clear();
    }
    else if (extensionType == "")
    {
        MessageBox.Show("Please Select An Extension Type", "Select File Type", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else if (txtDescription.Text.Length > 7999)
    {
        MessageBox.Show("File content cannot exceed 8000 characters", "Characters Exceeded", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    else {

        string fname = txtFileName.Text;
        string content = txtDescription.Text;
        string forUser = Form1.userId;
        string dateAdded = DateTime.Now.ToString();


        connection.Open();
        cmd = new SqlCommand("insert into [Files] values('" + fname + "', '" + extensionType + "', '" + content + "', '" + dateAdded + "', '" + forUser + "')", connection);
        cmd.ExecuteNonQuery();
        connection.Close();

        this.Alert("File Created Successfully");
        this.Alert("File Created Successfully");

        txtFileName.Clear();
        txtDescription.Clear();
        comboExtension.Text = ("File Extension");
    }
}

[Parameters](https://www.dbdelta.com/why-parameters-are-a-best-practice/) will solve your problem. — Dan Guzman, Apr 18 '21 at 10:16
It is a God send protection system to force you to use parameters. — Cetin Basoz, Apr 18 '21 at 10:30
I don't even know what parameters are as I am mostly self taught. but thank you I will read into it — Zwakele Sibisi, Apr 18 '21 at 10:36
you're not using `parameters` and you're not `sanitizing` your input. Try entering the following as a file name "myfile';delete files--" — Stu, Apr 18 '21 at 10:43
[Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Thom A, Apr 18 '21 at 11:03
I hear most of you guys talking about parameters which I get, but can someone please provide a code example of how to insert the data to database using those parameters. — Zwakele Sibisi, Apr 18 '21 at 11:22
@ZwakeleSibisi: Have you even had a brief look at the links people posted? There are examples. — sticky bit, Apr 18 '21 at 11:27
`txtDescription.Text.Length > 7999` this is wrong according to the error message. A length of 8000 does not exceed 8000 char limit. Given the name "txtDescription" it is strange that the error message refers to "File content". Smells like inconsistent user interface. — SMor, Apr 18 '21 at 11:42
You could also have a read of the [documentation](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.add?view=dotnet-plat-ext-5.0) @ZwakeleSibisi, which includes examples. — Thom A, Apr 18 '21 at 11:46
@SMor the name of the textbox is txtDescription but it holds the actual content of the file the user enters. It was named incorrectly when I made it so that's okay I'll sort that out. — Zwakele Sibisi, Apr 18 '21 at 13:21
Thanks for the feedback guys. I will look into all your suggestions. — Zwakele Sibisi, Apr 18 '21 at 13:21
Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Charlieface, Apr 18 '21 at 15:38
Thank you so much guys. It's working fine after using Parameters! — Zwakele Sibisi, Apr 18 '21 at 18:07

score 0 · Answer 1 · answered Apr 19 '21 at 08:46

Never, Ever, convert input parameters into part of a SqlCommand!

People with bad intensions will be able to delete your complete database:

var cmd = new SqlCommand("insert into [Files] values('"
    + fname
    + "', '"
    + extensionType ...

(You have more parameters, but I guess you'll get the gist)

What would happen if fname and extensionType would have the following values:

string fname = "TextFile.Txt', ExtensionType = `.Txt`, Content = `ABC`);"
   + "DROP DATABASE databasename;";

string extensionType = "insert into [Files] values('HaHaDeletedYourDB.Txt ...

This will make your complete sqlCommand:

Insert info [Files] values('TextFile.Txt', ExtensionType = `.Txt`, Content = `ABC`);
DROP DATABASE databasename;
insert into [Files] values(`HaHaDeletedYourDb.Txt`  ...

Your complete database will be removed Therefore: parameters should be added using DbParameterCollection.AddWithValue

public void AddToDb(string fileName, string extension, string content)
{
    const sqlText = "insert into [Files] values(@FileName, @Extension, @Content);"
    using (DbConnection dbConnection = new DbConnection(...))
    {
        using (var dbCommand = dbConnection.CreateCommand())
        {
            dbCommand.CommandText = sqlText;
            dbCommand.Parameters.AddWithValue(@FileName, fileName);
            dbCommand.Parameter.AddWithValue(@Extension, extension);
            dbCommand.Parameter.AddWithValue(@Content, content);

            dbConnection.Open();
            dbCommand.ExecuteNonQuery);
        }
    }
}

the using statement will make sure that Dispose is always called, even after an exception. Dispose will Close the DbConnection

Unclosed quotation mark after the character string

1 Answers1