Can select from but not insert into database mysql C# ASP.NET

Question

I have a connection to my db and I can retrieve data no problem but I'm unable to insert. The alert also isn't being displayed so I'm sure the query isn't being executed and it's just redirecting to the aspx page. I've tried many different solutions but I assume the problem is not where I'm looking. Here's the code, I hope someone can help.

Code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Threading;
using System.Threading.Tasks;

namespace Childrens.Admin
{
public partial class WebForm1 : System.Web.UI.Page
{
    protected void btnViewStaff_ServerClick(object sender, EventArgs e)
    {
        divAddStaff.Visible = false;
        staffGridView.Visible = true;
    }

    protected void btnAddNewStaff_ServerClick(object sender, EventArgs e)
    {
        staffGridView.Visible = false;
        divAddStaff.Visible = true;
    }

    protected void btnSubmitStaff_ServerClick(object sender, EventArgs e)
    {
        if (txtPassword == txtCPassword)
        {
            using (SqlConnection addStaffConn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConn"].ToString()))
            {
                try
                {
                    addStaffConn.Open();

                    string query = "INSERT INTO [Staff] (staff_fname,staff_sname,staff_email,staff_pass) VALUES ('" + txtFName + "','" + txtSName + "','" + txtEmail + "','" + txtPassword+"')"; //(@fname,@sname,@email,@pass)";
                    SqlDataAdapter staffAdapter = new SqlDataAdapter();
                    SqlCommand addStaffCommand = new SqlCommand(query, addStaffConn);

                    /*addStaffCommand.Parameters.AddWithValue("@fname", txtFName);
                    addStaffCommand.Parameters.AddWithValue("@sname", txtSName);
                    addStaffCommand.Parameters.AddWithValue("@email", txtEmail);
                    addStaffCommand.Parameters.AddWithValue("@pass", txtPassword);*/
                    staffAdapter.InsertCommand = addStaffCommand;
                    staffAdapter.InsertCommand.ExecuteNonQuery();


                    addStaffConn.Close();
                    Response.Write(String.Format("<script>alert('The entry was successful!');window.location='{0}';</script>", "URL=staff.aspx"));

                }
                catch (Exception ex)
                {
                    Response.Write(String.Format("<script>alert('The entry was successful!');window.location='{0}';</script>", "URL=staff.aspx"));
                }
                finally
                {
                    if (addStaffConn.State == System.Data.ConnectionState.Open)
                    {
                        addStaffConn.Close();
                    }
                    addStaffConn.Dispose();
                }
            }
        }
    }
}
}

web.config file:

<?xml version="1.0" encoding="utf-8"?>

<!--
For more information on how to configure your ASP.NET application, please 
visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<appSettings>
    <add key="ValidationSettings:UnobtrusiveValidationMode" value="None" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.6.1"/>
<httpRuntime targetFramework="4.6.1"/>
</system.web>
<system.codedom>
<compilers>
  <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
  <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
    type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, 
   Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, 
Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 
/define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
</compilers>
</system.codedom>
<connectionStrings>
<add name="myConn" connectionString="server=localhost;user 
id=root;persistsecurityinfo=True;database=childrens" />
<add name="childrensConnectionString" 
connectionString="server=localhost;user id=root;password=password;persistsecurityinfo=True;database=childrens;allowuservariables=True"
    providerName="MySql.Data.MySqlClient" />
 </connectionStrings>

 </configuration>

MySQL or SQL Server? I'm fairly sure you cannot use the (MS)SQL api for MySQL databases. — Uueerdo
– Uueerdo, Commented Apr 16, 2018 at 21:28
Unrelated Tips: the entire finally section is redundant and can be removed due to the using block. addStaffConn.Close(); is redundant for the same reason. SqlCommand and SqlDataAdapter are disposable so should be in using blocks. The catch block is reporting that it was successful. And although the AddWithValue is commented, I'd suggest reading can we stop using AddWithValue. — Richardissimo
– Richardissimo, Commented Apr 16, 2018 at 21:43
I didnt mention the Sql injection vulnerability, because I guessed you already knew, but I had second thoughts, and came back to mention it. — Richardissimo
– Richardissimo, Commented Apr 16, 2018 at 22:25
@Richardissimo I'm don't know much about sql security but the only vulnerability I can spot is that I have password set to password. is that what you were talking about? — Shakira Rhiane Salazar
– Shakira Rhiane Salazar, Commented Apr 16, 2018 at 22:32

Uueerdo · Accepted Answer · 2018-04-16 22:50:12Z

1

You should use MySQL's .Net Connector, instead of a library intended for MS SQL Server. Also, data adapters tend to be used more for data tables and similar things; for your needs simply executing "command" objects should be sufficient.

The security vulnerabilities everyone is referring to is that your query (at best) breaks the second an "fname", "sname", etc... contains one or more apostrophes; you should look into parameterized queries to avoid such issues.

Edit: Also, [ and ] are field delimiters for Microsoft databases (MS SQL Server, and MS Access); the ` (on the ~ key) is used by MySQL.

Edit#2: Vulnerability example:

INSERT INTO [Staff] (staff_fname,staff_sname,staff_email,staff_pass) VALUES ('" + txtFName + "','" + txtSName + "','" + txtEmail + "','" + txtPassword+"')"

User puts in their first name as O','','','then'), ('they', 'can', 'add', 'multiple'), ('users','or','possibly','worse'), ('without','even','causing','an'), ('error

edited Apr 16, 2018 at 22:50

answered Apr 16, 2018 at 22:41

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Shakira Rhiane Salazar Over a year ago

Thanks for pointing these out, I've tried it without field delimiters and I've had it as @fname etc. before but I've commented it out.

Shakira Rhiane Salazar Over a year ago

I've really just been trying a bunch of stuff. I'm using MySQL .NET connector 6.9.11

Uueerdo Over a year ago

System.Data.SqlClient is NOT MySQL's .Net Connector.

Uueerdo Over a year ago

You appear to have connection strings for both mysql and ms sql in your configuration (and are not using the mysql one); if you need database portability System.Data.Odbc might be more appropriate.

Shakira Rhiane Salazar Over a year ago

Thank you, that was very helpful. I'm obviously a noob and I appreciate the help. The code has a few more issues i'm working out but obviously the main issue was using the wrong connector.

|

Collectives™ on Stack Overflow

Can select from but not insert into database mysql C# ASP.NET

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related