How to dynamically insert in SQLite without SQLInjection?

Question

I have a function which receives data from another application. This data (user, password, repeatPassword) I want to insert into a SQLite DB and to avoid SQLInjection. How could I do this? My code:

        public class Data
        {
            public string user { get; set; }
            public string password { get; set; }
            public string repeatPass { get; set; }
        }

       [HttpPost, AllowCrossSite]
        [EnableCors(origins: "http://localhost:4200", headers: "*", methods: "*")]
        [Route("api/Register/Posthdf")]
        public IHttpActionResult Posthdf(Data data)        
        {
            using (SQLiteConnection conn = new SQLiteConnection("Data Source=C:\\ProiectVisualStudio\\Reporting_how-to-use-the-web-report-designer-in-javascript-with-angular-t566422-19.2.3-\\CS\\ServerSide\\App_Data\\RegisterDB.db; Version = 3; New = True; Compress = True; "))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        cmd.Connection = conn;
                        conn.Open();
                   //this inserts data correctly in DB: string strSql = "INSERT INTO tbl_Users (User, Password, RepeatPassword) VALUES('Teste1', 'Teste2', 'Teste3');";                    
                        strSql = "INSERT INTO tbl_Users (User, Password, RepeatPassword) VALUES(" + data.user + ", " + data.password + ", " + data.repeatPass + ");";                       
                        cmd.CommandText = strSql;
                        cmd.ExecuteNonQuery();
                        // do something…
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debug.WriteLine("***Error connection DB: " + ex + "/n");
                    }
                }
            }
    
            if (!ModelState.IsValid)
                return BadRequest("Invalid data");        

            return Ok(true);        
            
        }

You should look at this question https://stackoverflow.com/questions/293311/whats-the-best-method-to-pass-parameters-to-sqlcommand — Recep Gunes, Oct 13 '22 at 08:49

score 1 · Accepted Answer · answered Oct 13 '22 at 10:38

Use parameters:

strSql = "INSERT INTO tbl_Users (User, Password, RepeatPassword) VALUES(@user, @password, @repeatPassword);";                       
cmd.CommandText = strSql;
cmd.Parameters.AddWithValue("@user", data.user);
cmd.Parameters.AddWithValue("@password", data.password);
cmd.Parameters.AddWithValue("@repeatPassword", data.repeatPass);

How to dynamically insert in SQLite without SQLInjection?

1 Answers1