C# SQLCommand does not work

Question

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?

The first thing you're doing wrong is concatenating SQL strings. You should use query parameters instead. — David
– David, Commented May 30, 2012 at 15:04
You should use parameterized SQL for this; your code as currently written is vulnerable to SQL injection attack. I would make that change first; there's a chance the problem will go away when you do that. — Robert Harvey
– Robert Harvey, Commented May 30, 2012 at 15:05
You're also failing to put your SqlConnection, SqlCommand, and SqlDataReader in using blocks, and you're using ASMX web services when you should be using WCF unless you have no choice. — John Saunders
– John Saunders, Commented May 30, 2012 at 15:07
@David can you help me a little? Iam new at this and i dont know how to use query parameters — Bashud
– Bashud, Commented May 30, 2012 at 15:09

Damith · Accepted Answer · 2012-05-30 15:24:11Z

6

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;
}

edited May 30, 2012 at 15:24

answered May 30, 2012 at 15:07

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bashud Over a year ago

Thx that was "One" of my Problems :)

Damith Over a year ago

@Bashud updated my answer with 'using' statements, sql parameters, null checks etc. hope it will help you..

Bashud Over a year ago

Hey Damith i have a Question. Do i have to Close the connection or will that be done automatically?

Damith Over a year ago

You don't need to close it, it will handle automatically.

user957902 · Accepted Answer · 2012-05-30 15:12:19Z

3

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.

answered May 30, 2012 at 15:12

user957902

3,07017 silver badges19 bronze badges

Comments

akatakritos · Accepted Answer · 2012-05-30 15:11:18Z

1

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;

answered May 30, 2012 at 15:11

akatakritos

9,8781 gold badge25 silver badges30 bronze badges

Comments

Nathan Koop · Accepted Answer · 2012-05-30 15:09:05Z

0

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

answered May 30, 2012 at 15:09

Nathan Koop

25.3k26 gold badges95 silver badges127 bronze badges

Comments

Chetan S · Accepted Answer · 2012-05-30 15:16:54Z

0

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();

edited May 30, 2012 at 15:16

answered May 30, 2012 at 15:11

Chetan S

9451 gold badge11 silver badges21 bronze badges

2 Comments

Bashud Over a year ago

the informations iam passing are correct, i already checked that. Damith posted the Solution

Bashud Over a year ago

i could solved my Problem with the solution from Damith, but iam grateful for your help too :)

Collectives™ on Stack Overflow

C# SQLCommand does not work

5 Answers 5

4 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related