Keep getting System.Data.SqlClient.SqlException: 'Incorrect syntax near ';'.'

Question

I have read through many of the questions and answers provided in this forum regarding this question, applied many different methods and changed my code so many times I don't even remember the original.

I keep getting this error, on the following line:

SqlDataReader dr;

the error is

System.Data.SqlClient.SqlException: 'Incorrect syntax near ';'.'

I am a complete noob at this and I am self-teaching so I do apologise.

This is in App.Config

<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=SolAquaMasterDdata;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

My code on the main form:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace SolTry
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        SqlConnection conn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        
        public MainWindow()
        {
            InitializeComponent();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
        }

        private void BtnLogin_Click(object sender, RoutedEventArgs e)
        {
            string User = txtUsername.Text;
            string Pass = txtPassword.Password;

            string str1 = "Please enter a valid Username and Password.";
            string str2 = "The credentials entered do not match any registed users.";
            string str3 = "These login credentials are correct.";

            conn.Open();
            cmd.Connection = conn;
            SqlDataReader dr;
            cmd.CommandText = ("SELECT Status, UserName, Password FROM tblUsers WHERE UserName = @Username and Password = @Password;");
            using (conn)
            {
                //help add parameterization - missing 
                dr = cmd.ExecuteReader();

                if ((string.IsNullOrEmpty(User)) && (string.IsNullOrEmpty(Pass)))
                {
                    MessageBox.Show(str1, "NO CREDENTIALS ENTERED", MessageBoxButton.OK, MessageBoxImage.Error);
                }

                if ((string.IsNullOrEmpty(User)) == false && (string.IsNullOrEmpty(Pass)) == false)
                {
                    if (dr.HasRows.Equals(true))
                    {
                        MessageBox.Show(str3, "LOGIN SUCCESSFUL", MessageBoxButton.OK, MessageBoxImage.Information);
                    }

                    else if (dr.HasRows == false)
                    {
                        MessageBox.Show(str2, "INVALID CREDENTIALS", MessageBoxButton.OK, MessageBoxImage.Error);
                    }
                }
            }
            conn.Close();
        }

        private void ExitApp(object sender, RoutedEventArgs e)
        {
            Application.Current.Shutdown();
        }

        protected override void OnMouseLeftButtonDown(MouseButtonEventArgs e)
        {
            base.OnMouseLeftButtonDown(e);
            DragMove();
        }
    }
}

No matter what I have tried I keep failing.

All I am trying to do is make the login form button on click verify that the username and password are correct in the tblUsers and then check that the Status is "true" or 1

Please show me how to parameterize the sql

Answer 1

you are not sending any parameters to the command. parameterize your query and the sql will work

   cmd.CommandType = System.Data.CommandType.StoredProcedure;
   cmd.CommandText = ("SELECT Status, UserName, Password FROM tblUsers WHERE UserName = @Username and Password = @Password;");

   cmd.Parameters.Add("@UserName", SqlDbType.Varchar, 255).Value = txt.Username;
    cmd.Parameters.Add("@Password", SqlDbType.Varchar, 255).Value = txt.Password;

    dr = cmd.ExecuteReader();

Do the password encryption and decryption in the database using hashbyte. The client then uses text only. Don't do hashing on the client side. keep it simple.

  See. https://www.mssqltips.com/sqlservertip/4037/storing-passwords-in-a-secure-way-in-a-sql-server-database/

  Hashbytes

Answer 2

This line of code has mismatched parenthesis within the quoted query text.

cmd.CommandText = ("SELECT Status, UserName, Password 
                    FROM tblUsers WHERE(UserName = txt.Username 
                                        and Password = txt.Password;");

There is an open parenthesis after the WHERE, and there is not a close parenthesis within the quoted text. That is why you are getting a SQL exception. You can either remove the opening parenthesis, or add a closing parenthesis.

Additionally, you need to use Parameters to pass in the username and password values. The strings txt.Username and txt.Password won't have any meaning to the SQL server.

Answer 3

There are many issues with your existing code.

• Primarily, you are trying to refer to C# objects from within SQL. You cannot do that, as the server does not see your client code at all. Instead use proper parameterization.
• You had a missing/extra parenthesis
• You need to create and dispose your connection, command and reader object with using blocks, at the point of use. You then don't need to explicitly close, the using will close it for you.
• Don't block the thread with a message box while the connection is open
• It's not actually necessary to use a reader, because you only want to check for existence of a single row. Just SELECT 1 and use cmd.ExecuteScalar()
• Do not store or pass plain-text passwords. Hash the password in the client, and pass the hash to the server to check.

if ((string.IsNullOrEmpty(User)) && (string.IsNullOrEmpty(Pass)))
{
    MessageBox.Show(str1, "NO CREDENTIALS ENTERED", MessageBoxButton.OK, MessageBoxImage.Error);
    return;
}

bool isMatch;

const string query = @"
SELECT 1
FROM tblUsers u
WHERE u.UserName = @Username
  and u.PasswordHash = @PasswordHash;
";
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString()))
using (var cmd = new SqlCommand(query, conn))
{
    cmd.Parameters.Add("@UserName", SqlDbType.NVarchar, 250).Value = txt.Username;
    cmd.Parameters.Add("@PasswordHash", SqlDbType.Binary, 32).Value = YourPasswordHashFunctionHere(txt.Password);
    conn.Open();
    isMatch = ((int?)cmd.ExecuteScalar()) == 1;
}

if (isMatch)
{
    MessageBox.Show(str3, "LOGIN SUCCESSFUL", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
    MessageBox.Show(str2, "INVALID CREDENTIALS", MessageBoxButton.OK, MessageBoxImage.Error);
}