CODEWITHSUNDEEP
c#asp.netsql-server
Raja Gopal
Raja Gopal

Reputation: 51

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.' is that sql query error or something?

I'm using Visual Studio 2019 and SQL Server Management Studio.

CustomerDetails.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomerDetails.aspx.cs" Inherits="CustomerInfo.CustomerDetails" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .auto-style1 {
            text-align: center;
        }
        .auto-style2 {
            width: 29%;
            height: 119px;
            margin-left: 51px;
        }
        .auto-style3 {
            width: 240px;
        }
        .auto-style4 {
            width: 240px;
            text-align: right;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h3 class="auto-style1">Enter Details</h3>
        </div>
        <p>
            &nbsp;</p>
        <table align="center" class="auto-style2">
            <tr>
                <td class="auto-style4">User Name :</td>
                <td>
                    <asp:TextBox ID="name" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="auto-style4">Email :</td>
                <td>
                    <asp:TextBox ID="mail" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="auto-style4">Password :</td>
                <td>
                    <asp:TextBox ID="password" runat="server" Width="200px"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="auto-style3">&nbsp;</td>
                <td>
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Submit" Width="100px" />
                </td>
            </tr>
        </table>
        <asp:Label ID="Label1" runat="server" ForeColor="Green" Visible="False"></asp:Label>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Customer_Info_TableConnectionString %>" SelectCommand="SELECT * FROM [cus_table]"></asp:SqlDataSource>
    </form>
</body>
</html>

I got the exact output how I created the webpage design, and it allow me to enter the details in the field after I pressing the button I get this error:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code
Incorrect syntax near ')'.

CustomerDetails.aspx.cs

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

namespace CustomerInfo
{
    public partial class CustomerDetails : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Customer_Info_TableConnectionString"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("insert into [dbo].[cus_table]('" + name.Text+"','"+name.Text+"','"+mail.Text+"','"+password.Text+"')",con);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            Label1.Visible = true;
            Label1.Text = "Information uploaded Successfully! :)";

            name.Text = "";
            mail.Text = "";
            password.Text = "";
        }
    }
}

web.config

<connectionStrings>
    <add name="Customer_Info_TableConnectionString" 
         connectionString="Data Source=DESKTOP-B44TBSQ;Initial Catalog=Customer_Info_Table;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

SQL Server:

CREATE TABLE [dbo].[cus_table]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NOT NULL,
    [mail] [nvarchar](50) NOT NULL,
    [password] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

Upvotes: 2

Views: 1305

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062855

  1. never ever concatenate values to create SQL (see: SQL injection); use parameters, ideally with a tool like dapper
  2. it is adviseable to specify the column order when inserting to prevent ambiguities
  3. never ever store passwords as text; hash them with salt for authentication (long boring but important topic), or use strong encryption if you're writing a password manager

This example uses Dapper for convenience; note that Dapper also deals opening and closing the connection, parameter packing, and (when using Query<T> etc) parsing results into instances. All the boring bits of ADO.NET!

var pwHash = // ... not shown; something involving password.Text
             // note: you'd usually need to invent and store the "salt", too
conn.Execute(@"
    insert into [dbo].[cus_table] (name, mail, passwordHash)
    values (@name, @mail, @pwHash)",
    new {
        name = name.Text,
        mail = mail.Text,
        pwHash = pwHash
    });

Label1.Visible = true;
Label1.Text = "Information uploaded Successfully! :)";

Note: the above wont work directly because you have a password column not a passwordHash column. But importantly: your password column is just a thing that should not exist. I can't force you to fix it, but: please please do.

The bad security version would be something like:

conn.Execute(@"
    insert into [dbo].[cus_table] (name, mail, password)
    values (@name, @mail, @password)",
    new {
        name = name.Text,
        mail = mail.Text,
        password = password.Text // TODO: hash (for auth) or encrypt (pw manager)
    });

Upvotes: 5

Related Questions