SQL connection problem when I try to use it 2 times

Question

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("Insert 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("Insert 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

Use sql parameters to prevent sql injection which is easy if you read the CardNumber from the console-input. — Tim Schmelter, Nov 25 '22 at 18:13
@TimSchmelter I dont know how to do it I'm going to search to see if I can find anything, thanks — , Nov 25 '22 at 18:16

score 1 · Accepted Answer · answered Nov 25 '22 at 18:53

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.

score 0 · Answer 2 · answered Nov 25 '22 at 18:03

0

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.

answered Nov 25 '22 at 18:03

Sarath Baiju

226
2
9

tell me this: There is already an open DataReader associated with this Command which must be closed first. So I need to close the connection ? (cn.Open()) ? – Nov 25 '22 at 18:09
tried to close and reopen and gave another error : Index was outside the bounds of the array. – Nov 25 '22 at 18:12
did you add MultipleActiveResultSets=true in the connection string? eg: "Data Source=MAD-PC-023;Database=atmbd;Trusted_Connection=True;MultipleActiveResultSets=true" – Sarath Baiju Nov 25 '22 at 18:34

SQL connection problem when I try to use it 2 times

2 Answers2