My WinForm is getting a SQL Injection

Question

my registration application seems to be pulling a SQL Injection when I run it, anyone have any quick fix suggestions for me, I would really prefer to not change everything but to make this able to run for my first project update.

string constring = "datasource=127.0.0.1;port=3306;username=root;password=welcome";
        string Query = "insert into userdatabase.users (userid, email, passone, passtwo, lastname, firstname) values('" + this.userid_txt.Text + "','" + this.email_txt.Text + "','" + this.passone_txt.Text + "','" + this.passtwo_txt.Text + "','" + this.lastname_txt.Text + "','" + this.firstname_txt.Text + "') ;";
        MySqlConnection conDataBase = new MySqlConnection(constring);
        MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase);
        MySqlDataReader myReader;
        try
            {
                conDataBase.Open();
                myReader = cmdDataBase.ExecuteReader();
                MessageBox.Show("Welcome to iDSTEM!");
                while (myReader.Read())
             {

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

The error states that:

You have an error in my SQL syntax; check the manual... check syntax near "userdatabase" at line 1

Yes, that code is susceptible to SQL injection (and just good old fashioned failure for anything involving an apostrophe). So fix that. What is the question? — Marc Gravell, Apr 30 '14 at 13:17
Which sections of this code causes SQL injection everything I researched involves single ' marks which are not effecting the results — Bendlino, Apr 30 '14 at 13:18
erm, all of it. Each and every one of those `'" + something.Text + "'` is a SQL injection hole — Marc Gravell, Apr 30 '14 at 13:19
It is considered bad form to edit the question in such a way as to make the answers nonsensical; I'm going to revert that last edit. With regards to the problem: `SqlDbType` is SQL-Server specific, and should not be used with mysql; use `DbType.String` instead — Marc Gravell, Apr 30 '14 at 13:56

apomene · Answer 1 · 2014-04-30T14:40:09.540

3

inorder to avoid injection you must use parameters, e.g:

string constring = "datasource=127.0.0.1;port=3306;username=root;password=welcome";
string Query = "insert into userdatabase.users (userid, email, passone, passtwo, lastname, firstname) values(@par1,@par2,@par3,@par4,par5,@par6)";

    MySqlConnection conDataBase = new MySqlConnection(constring);
    MySqlCommand cmd = new MySqlCommand(Query, conDataBase);
    cmd.Parameters.AddWithValue( "@par1",this.userid_txt.Text)
    cmd.Parameters.AddWithValue("@par2",this.email_txt.Text)
    cmd.Parameters.AddWithValue("@par3",this.passone_txt.Text)
    cmd.Parameters.AddWithValue( "@par4",this.passtwo_txt.Text )
    cmd.Parameters.AddWithValue("@par5",this.lastname_txt.Text)
    cmd.Parameters.AddWithValue("@par6",this.firstname_txt.Text)

    try
         {
           conDataBase.Open();
           //Execute command
           cmd.ExecuteNonQuery(); ///I suppose no need to use datareader...since you make insert
          MessageBox.Show("Welcome to iDSTEM!");

     catch (Exception ex)
           {
              MessageBox.Show(ex.Message);
           }

edited Apr 30 '14 at 14:40

answered Apr 30 '14 at 13:22

apomene

14,282
9
46
72

Thanks, saved me typing it. I'd also suggest showing the OP how to modify the SQL statement itself to include the parameters. – Liath Apr 30 '14 at 13:24
Multiply that by 6 and tools like "dapper" start to look **really** appetizing! – Marc Gravell Apr 30 '14 at 13:25
Apomene, I get an object reference required for MySql.Data.MySqlClient.MySqlCommand.Parameters.get, other than that no errors found – Bendlino Apr 30 '14 at 14:36
@apomene that fixed the issue but I am receiving a fatal error, running through my code now and ideas on what would cause this error after I attempt to connect to the database? Really appreciate your tips so far – Bendlino Apr 30 '14 at 14:49

score 1 · Answer 2 · answered Apr 30 '14 at 13:24

1

The "fix" here is parameters. As for short cuts: if the database provider supports named parameters, dapper could help. I don't use mysql, so I can't say for sure, but if we imagine that mysql supports named parameters using the @ prefix for parameter names, then:

// uses "dapper" (search for "Dapper" on NuGet / Package Manager)
connection.Execute(
@"insert into userdatabase.users
      (userid, email, passone, passtwo, lastname, firstname)
values(@userid, @email, @passone, @passtwo, @lastname, @firstname)",
    new { userid = this.userid_txt.Text,
          email = this.email_txt.Text,
          passone = this.passone_txt.Text,
          passtwo = this.passtwo_txt.Text,
          lastname = this.lastname_txt.Text,
          firstname = this.firstname_txt.Text} );

answered Apr 30 '14 at 13:24

Marc Gravell

1,026,079
266
2,566
2,900

I've not seen that approach before - nice! – Liath Apr 30 '14 at 13:41
Marc, the only thing I could not get going on this was the connection 'does not exist in current context' do I have to call this connection somewhere above? – Bendlino Apr 30 '14 at 13:54
@user3581876 you already had a connection - it was called `conDataBase`. The `Execute` method comes via "dapper" – Marc Gravell Apr 30 '14 at 13:55

My WinForm is getting a SQL Injection

2 Answers2