SQLException: Unclosed quotation mark after the character string '''

Question

I have a problem in my login system.

When I add the apostrophe character (') I get the error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in Microsoft.Practices.EnterpriseLibrary.Data.dll

Additional information: Unclosed quotation mark after the character string '''. Incorrect syntax near '''.

Here is the the login code. This is taking the username and password separately, but it works.

public String getSenha(string user)
    {
        String Query = "SELECT senha FROM dbo.Login WHERE usuario = '" + user + "'"; //Comando

        Conexao Connection = new Conexao(); //Instancia a classe conexao
        object ret = Connection.QueryScalar(Query); //Executa o comando e salva o resultado em 'ret'

        if (ret.GetType() == typeof(int))
            return null;
        else
            return (string)ret;
    }

public Boolean checkUser(string user)
    {
        String Query = "SELECT COUNT(usuario) FROM dbo.Login WHERE usuario = '" + user + "'";
        Conexao Connection = new Conexao();

        if ((int)Connection.QueryScalar(Query) > 0) // renorno de 0 significa que nao existe esse usuario.
            return true;
        else
            return false;
    }

public Object QueryScalar(string Command)
    {
        // Error is thrown here
        return DatabaseFactory.CreateDatabase("Windows.Properties.Settings.dboSoftwareGSCConnectionString").ExecuteScalar(CommandType.Text, Command);
    }

Answer 1

This is classic SQL injection. In SQL, statements are encased in single quotes... So your single quote ends your statement prematurely since you're not escaping it. This also leaves you vulnerable to attack from malicious users. You must use parameterized SQL to avoid it. See: http://www.dotnetperls.com/sqlparameter