CODEWITHSUNDEEP
c#sqlsqlcommand
Bashud
Bashud

Reputation: 276

C# SQLCommand does not work

I have a WebService with the following Method:

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    [WebMethod]
    public string Login(string passwort, string email, string firma)
    {
        return LoginHelper.Login(passwort, email, firma);
    }

My LoginHelper code:

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace WebService1
{
    public class LoginHelper
    {
        public static string Login(string passwort, string email, string firma)
        {
            string userName = "";

            SqlConnection con = new SqlConnection(@"Data Source=Yeah-PC\SQLEXPRESS;Initial Catalog=works;Integrated Security=true;");

        SqlCommand cmd = new SqlCommand(@"SELECT firma FROM TestData 
                                  WHERE email = @email", con);
        cmd.Parameters.AddWithValue("@email", email);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
      {
   //userName += dr["email"].ToString();
   //userName += dr["passwort"].ToString();
   userName += dr["firma"].ToString();
     }
    dr.Close();
    con.Close();
    return userName;
        }



    }
}

Thanks for that help Guys

I have edited my Questions. Is that solution secure now? I mean against SQL-Injection. Is there something more what i can do better?

Upvotes: 0

Views: 928

Answers (5)

Damith
Damith

Reputation: 63065

you are calling LoginHelper.Login(passwort, email, firma);

but in your method

public static string Login(string email, string passwort, string firma)

email is fist parameter.

actually in email parameter you have password, that's why it not return any result

change your login method in LoginHelper as below

public static string Login(string passwort, string email, string firma)
{
    string userName = "";

    using (SqlConnection con = new SqlConnection(@"Data Source=Yeah-PC\SQLEXPRESS;Initial Catalog=works;Integrated Security=true;"))
    using(SqlCommand cmd = new SqlCommand(@"SELECT firma FROM TestData WHERE email = @email", con))
    {
        cmd.Parameters.AddWithValue("@email", email);
        con.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                if (rdr["firma"]  != DBNull.Value)
                {
                    userName += rdr["firma"].ToString();
                }

            }
        }
    }

    return userName;
}

Upvotes: 6

Chetan S
Chetan S

Reputation: 945

Instead of passing parameter in code try to use Sqlparameter to add parameter. It is best practice to use SQL parameter to add parametrs. You can also check the value of email by debugging....wether you are passing correct information. 

        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(@"SELECT firma FROM TestData 
                              WHERE email = @email" conn);
        cmd.Parameters.AddWithValue("@email", email);                     
        cmd.Prepare();
        cmd.ExecuteNonQuery();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
            {                  
               userName += dr["firma"].ToString();

            }
        dr.Close();
        conn.Close();

Upvotes: 0

user957902
user957902

Reputation: 3060

If your email address contains an @ character, that may be your problem. The @ is a parameter marker for SQLCommand. It is going to think the latter part of your e-mail address is a sql parameter. You will need to pass the e-mail address using a parameter. That also protects you from SQL injection. Akatakritos's answer has an example of how to pass the email as a prameter.

Upvotes: 3

akatakritos
akatakritos

Reputation: 9858

Also, for security and performance reasons, you should be using SqlParameters. Read up on SQL Injection attacks.

string userName = "";

SqlConnection con = new SqlConnection(@"Data Source=Yeah-PC\SQLEXPRESS;Initial Catalog=works;Integrated Security=true;");

SqlCommand cmd = new SqlCommand(@"SELECT firma FROM TestData 
                                  WHERE email = @email" con);
cmd.Parameters.AddWithValue("@email", email);

con.Open();

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
   //userName += dr["email"].ToString();
   //userName += dr["passwort"].ToString();
   userName += dr["firma"].ToString();
}
dr.Close();
con.Close();
return userName;

Upvotes: 1

Nathan Koop
Nathan Koop

Reputation: 25197

Per the other answer & comments.

You have a security issue, if you're not going to use an ORM (Entity Framework/NHibernate/etc...) please use parameterized queries

Solving your problem:

  • Is data in your database?
  • Are you pointing at the right database?
  • Is your SQL correct?
  • Is your SQL getting run?
  • Run SQL Profiler and see what SQL is getting run, then test that in your SQL Management Studio

Upvotes: 0

Related Questions