Not for nothing, but can someone post a version of that code that would be passable? I'm not too familiar with .NET and it's libraries, so I'm just curious as to how it should be structured.
The SQL injection problem is easy to fix - escape single quotes manually, use parametrized queries, use LINQ, etc. The bigger problem with this code is security design, or lack of one as it were. To make this code right one has to come with a secure password storage scheme, and, by the looks of it, a secure session authentication mechanism is also required. By the time you're done coding it will be completely different code, and a bit more of it than is present.
Here's where you would start:
1. The password should be stored only after being hashed, with a unique salt, preferably using a hardened hash function such as PBKDF2 (part of standard .NET library).
2. Session mechanism would involve a server-side secret, a user id, and an expiration date, all hashed together using a HMAC function (also part of the standard .NET library). This session token will be shuffled back and forth, and the HMAC signature is to be validated by the server on each request (including the expiration date).
Variable, method, and file names, having all code in the click handler etc are the least of the problems here. Frankly, which problems people pick in this code is probably a better indicator of skills and experience than the code itself lets on about the original author.
This much work should not be happening in a button's click event handler. It should be its own method (or more than one!) called by the click handler.
For that matter, data access should be handled by a DAO, front end code should be calling a service or gateway rather than going to the DB itself. Way too much is happening in GUI code.
In a "real" app, the connection string would be configured in some way, not just a local variable.
The way the query is written is wide open to SQL injection. Nobody should be writing SQLI vulns in this day and age, ever. In .NET land, if you're still concatenating queries instead of using query parameters, you should go home because you are bad at your job.
The list could go on. Some of this can be excused by the fact that it's from an interviewee and not part of a "real" app. But the SQLI is just a huge red flag, to me. One thing they did get right: wrapping the connection object in a "using" block so it is guaranteed to be disposed of correctly.
True, but the page redirect happens within the using block. I would assume that the disposal still happens, but this is very very bad form.