Sql connection Problem when I try to use it 2 times-CodePudding

I'm trying to create a project of an ATM where you have to enter the card number and pin but it's not working when I put the right pin says "Pin not found!" which is the catch but I copied the code from above and just changed what I thought necessary, does anyone know what's wrong?

    static void Main()
    {
        using (var cn = new SqlConnection("Data Source=MAD-PC-023;Database=atmbd;Trusted_Connection=True;"))
        {
            cn.Open();
            string debitCard = "";
            Console.WriteLine("Inser your card number: ");
            while (true)
            {
                try
                {
                    debitCard = Console.ReadLine();
                    if (debitCard.Length != 8)
                    {
                        Console.WriteLine("Wrong format!");
                    }
                    else { 
                        //falta algum IF EXISTS IN DB 
                        using (var cmd = new SqlCommand() { Connection = cn, CommandText = "SELECT FirstName FROM atm WHERE CardNumber = '"   debitCard   "'" })
                        {
                            var reader = cmd.ExecuteReader();
                            if (reader.Read() == true)
                            {
                                Console.WriteLine("Hi, "   reader.GetString(0));
                                break; 
                            }
                            else
                            {
                                Console.WriteLine("Not found");
                            }
                        }
                    }
                    
                }
                catch { Console.WriteLine("Not found!"); }
            }
            string pin = "";
            Console.WriteLine("Inser pin ");
            while (true)
            {
                try
                {
                    pin = Console.ReadLine();
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = "SELECT FirstName, LastName FROM atm WHERE Pin = '"   pin   "'" })
                    {
                        var reader = cmd.ExecuteReader();
                        if (reader.Read() == true)
                        {
                            Console.WriteLine("User Found");
                            break;
                        }
                        else
                        {
                            Console.WriteLine("Not found!");
                        }
                    }

                }
                catch { Console.WriteLine("Pin not found!"); }
            }
        }
    }
}

I've tried many different ways and i can't do it, If anyone can help me, I'd be grateful

CodePudding user response：

Currently, your catch block not taking exception object. So it is difficult to find the exact root cause of this issue. So change your catch block as below

catch(Exception ex) { Console.WriteLine(ex.Message); }

From the exception object, you will get the exact root cause of your issue.

CodePudding user response：

There are many issues in your existing code. Let me highlight few of them.

You should use parameterized query, you code is open to SQL Injection.

I don’t know why are you storing card number and pin in your database tables. It’s against PCIDSS standards, you need to read about it.

You cannot store the card number as plain text, I don’t know about current standards, but earlier it was that you should mask the card while storing and now I think it’s token based ( at lest in india now).

And you can’t at all store the PIN number in database as plain text.

You need to store the encrypted PIN number.

And there is no one-one mapping for your card number and pin. Because same pin can be used by many people, so you need one-one mapping as well.

You should simplify your query to IF EXISTS SELECT 1 FROM …

If you simplify your code then you will realize that you need to rerun only one item from database, so you could use ExecuteScalar for the same.

And last but not the least, put breakpoint and debug your code.