Sql interjection error faced

Question

0.00/5 (No votes)

See more:

private void button1_Click(object sender, EventArgs e)
    {
        if (comboBoxstaff.Text == string.Empty)
        {
            MessageBox.Show("Please Select gaugeman");  // not to let thecombobox empty
            return;
        }
        else if (comboBoxcompondrubber.Text == string.Empty)
        {
            MessageBox.Show("Please Select Compond Rubber");// not to let thecombobox empty
            return;

        }
        else if (textBox1.Text == string.Empty)
        {
            MessageBox.Show("Please Key in W.S thickness");// not to let thetextbox empty
            return;

        }
        else if (textBox2.Text == string.Empty)
        {
            MessageBox.Show("Please Key in G.S thickness");// not to let thecombobox empty

        }
        SQLiteConnection insertsess = new SQLiteConnection("Data Source=|DataDirectory|\\test1db");
        string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES ('" + comboBoxstaff.Text + "','" + label2.Text + "', '" + comboBoxcompondrubber.Text + "', '" + textBox2.Text + "', '" + textBox1.Text + "')";   //insert statment
        SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
        insertsess.Open();
        ins1.ExecuteNonQuery();
        MessageBox.Show("Data had been saved");// showed when the message is being saved

        SQLiteConnection sesscheck = new SQLiteConnection("Data Source=|DataDirectory|\\test1db");
        SQLiteCommand chk1;
        chk1 = sesscheck.CreateCommand();
        chk1.CommandText = "SELECT GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes FROM thickness WHERE CompondRubber = '" + comboBoxcompondrubber.Text.Trim() + "'";
       sesscheck.Open();
        DataTable thicknessTable = new DataTable();
        //DataTable thicknessTable = new DataTable();
        SQLiteDataReader reader = chk1.ExecuteReader();
        thicknessTable.Load(reader);
        //SQLiteDataReader reader1 = chk2.ExecuteReader();
        //thicknessTable.Load(reader1);
        sesscheck.Close();

        dt = new DataTable();
        sda.Fill(dt);
        dataGridView1.DataSource = dt;

    }

What I have tried:

I had tried using data adapter but it didn't work I tried using data reader but I am not sure on how to go about using it properly.

I want to get the value 12.0 to be saved and displayed into data grid view at the moment if I key 12.1 it also works only like 12.0 it will be entered and displayed as 12 at the data grid view

Posted 6-Jun-18 15:44pm

Member 13809133

Updated 6-Jun-18 21:54pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-06-06T18:37:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

It doesn't matter if you use a DataReader or a DataAdapter - SQL injection starts when you concatenate strings. So this code is a problem:

C#

string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES ('" + comboBoxstaff.Text + "','" + label2.Text + "', '" + comboBoxcompondrubber.Text + "', '" + textBox2.Text + "', '" + textBox1.Text + "')";   //insert statment
SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
insertsess.Open();
ins1.ExecuteNonQuery();

Convert it (all the rest of your app) to a parameterised query:

C#

string insert12 = "INSERT INTO thickness (GaugeMan,Dateandtime, CompondRubber,GSthickness,WSthicknes) VALUES (@GM, @DT, @CR, @GST, @WST)"
SQLiteCommand ins1 = new SQLiteCommand(insert12, insertsess);
ins1.Parameters.AddWithValue("@GM", comboBoxstaff.Text);
ins1.Parameters.AddWithValue("@DT", label2.Text);
ins1.Parameters.AddWithValue("@CR", comboBoxcompondrubber.Text);
ins1.Parameters.AddWithValue("@GST", textBox2.Text);
ins1.Parameters.AddWithValue("@WST", textBox1.Text);
insertsess.Open();
ins1.ExecuteNonQuery();

Note that you are responsible for closing and disposing of all database access objects, us it's also a very good idea to use a using block for teh Connection, Command, and suchlike.
I would also put the Connection.Open call close to the Connection construction, rather than mixed into the code further down. That way it's less likely to cause problems when you modify or copy the code.

CHill60 · Answer 2 · 2018-06-06T21:56:00

Picking up on the other problem you mention

Quote:
I want to get the value 12.0 to be saved and displayed into data grid view at the moment if I key 12.1 it also works only like 12.0 it will be entered and displayed as 12 at the data grid view

The display has nothing to do with the way the number is stored or how you are getting it back from the database. You have to tell the dataGridView how to display it.

For example, right-click dataGridView1 and set the defaultCellStyle of the appropriate column(s) to "N2" (Numeric).

This assumes that you are storing the information on the database in a numeric, non-integer column type. If not then you need to change your table schema so that the column types match the type of data you want to store in them.

Sql interjection error faced

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0