String to sqlsyntax

Question

I have a string:

string theUserId = Session["UserID"].ToString();

But I dont know how to add the string to this sqlsnytax

    {
        if (Session["UserID"] != null) 
        {
            string theUserId = Session["UserID"].ToString();
            Label1.Text = Convert.ToString(theUserId);


        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=x; Password=x;");
        cn.Open();
        OdbcCommand cmd = new OdbcCommand("SELECT User.FirstName, User.SecondName, User.Aboutme, User.DOB, Pictures.picturepath FROM User LEFT JOIN Pictures ON User.UserID = Pictures.UserID WHERE User.UserID=@UserID"), cn);

        cmd.Parameters.AddWithValue("@UserID", theUserId);

        OdbcDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Name.Text = String.Format("{0} {1}", reader.GetString(0), reader.GetString(1));
            Aboutme.Text = String.Format("{0}", reader.GetString(2));
            Age.Text = String.Format("{0}", reader.GetString(3));
            Image1.ImageUrl = String.Format("{0}", reader.GetString(4));
        }


    }
}
}

User.UserID=1 how would I change that to something like User.UserID="theUserId"

@Brandon has the correct answer given the requirements; however, a couple things should be noted. First, "theUserId" needs to be sanitized prior to execution. As it stands this has SQL injection written all over it. Second, I'm not sure what it is you are passing this too but it sure looks like someone needs to rip it out. You might want to post some of the code over at codereview.stackexchange.com — ChrisLively
– ChrisLively, Commented Mar 21, 2011 at 19:32
@ukhardy, deleted. WraithNath essentially has the same answer without all the noise. I removed mine and upvoted his instead. — Brandon
– Brandon, Commented Mar 21, 2011 at 19:53

ChrisLively · Accepted Answer · 2011-03-21 19:49:05Z

See the following. The number one thing to note is the USING clauses which will clean up your connections. Either you use these or you have to wrap everything in try .. catches with the appropriate disposing calls made.

if (Session["UserID"] != null) 
{
    string theUserId = Session["UserID"].ToString();
    Label1.Text = Convert.ToString(theUserId);

    using (OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=root; Password=commando;")) {
        cn.Open();
        using (OdbcCommand cmd = new OdbcCommand("SELECT User.FirstName, User.SecondName, User.Aboutme, User.DOB, Pictures.picturepath FROM User LEFT JOIN Pictures ON User.UserID = Pictures.UserID WHERE User.UserID=@UserID", cn)) {

            cmd.Parameters.AddWithValue("@UserID", theUserId);

            using (OdbcDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read())
                {
                    Name.Text = String.Format("{0} {1}", reader.GetString(0), reader.GetString(1));
                    Aboutme.Text = String.Format("{0}", reader.GetString(2));
                    Age.Text = String.Format("{0}", reader.GetString(3));
                    Image1.ImageUrl = String.Format("{0}", reader.GetString(4));
                }
            } // using reader
        } // using cmd
    } // using connection
}

WraithNath · Accepted Answer · 2011-03-21 19:41:41Z

1

string theUserId = Session[ "UserID" ].ToString();

OdbcCommand cmd = new OdbcCommand(
      "SELECT User.FirstName, User.SecondName, User.Aboutme, User.DOB, Pictures.picturepath FROM User LEFT JOIN Pictures ON User.UserID = Pictures.UserID WHERE User.UserID=@UserID" 
    ), cn);

   cmd.Parameters.AddWithValue("@UserID", theUserId);

You can define your parameters with @Parameter name and then add them using .Parameters.AddWithValue

This is much safer than string.format or concatinating the string yourself

edited Mar 21, 2011 at 19:41

answered Mar 21, 2011 at 19:36

WraithNath

18.1k10 gold badges59 silver badges84 bronze badges

2 Comments

G Gr Over a year ago

im getting an error on the line ,cn); its really strange because this was working be4 I added in my string to it? iv re edited so you can see

Brandon Over a year ago

@Garrith, no, it wasn't working before. In the sample you posted you have an extra closing ) after your string. Remove it.

kprobst · Accepted Answer · 2011-03-21 19:24:44Z

0

 WHERE User.UserID = $UserID

Then add a parameter called '$UserID' to the Command object you're using, and it will pick up the value when you execute the query.

Note that I'm not sure what driver you're using for MySQL, I think parameters must be prefixed with $, but I'm not 100% sure. In SQL Server it's @.

answered Mar 21, 2011 at 19:24

kprobst

16.8k5 gold badges35 silver badges53 bronze badges

5 Comments

kprobst Over a year ago

The question is tagged with MySQL. There are managed MySQL drivers for .NET, which I've used before. I assumed the poster is using MySQL.

ChrisLively Over a year ago

True. However the $UserId notation is php specific and not MySql syntax. MySql uses the same variable syntax that other RDBMs use. For example: @UserId See dev.mysql.com/doc/refman/5.0/en/user-variables.html for a little more information

kprobst Over a year ago

It's not PHP-specific. Back in the 1.1 days the MySQL driver sponsored by MySQL AB required something other than a @ character. I just don't remember which one it was. If they standardized it to be @ then that's great. It's been a while.

ChrisLively Over a year ago

@kprobst: I'll make you a deal. If you can point to any MySQL documentation that uses the $ notation for variables that isn't participating in php string replacement, no matter the version, then I will happily eat crow and change my vote. ;)

kprobst Over a year ago

That ain't gonna happen, sorry. I don't care enough about your vote to waste my time digging for something you claim I'm making up. Cheers.

R_F · Accepted Answer · 2011-03-21 19:30:47Z

0

Are you looking for something like this?

string.Format("SELECT User.FirstName, User.SecondName, User.Aboutme, User.DOB, Pictures.picturepath FROM User LEFT JOIN Pictures ON User.UserID = Pictures.UserID WHERE User.UserID={0}", theUserId);

answered Mar 21, 2011 at 19:30

R_F

315 bronze badges

Collectives™ on Stack Overflow

String to sqlsyntax

4 Answers 4

Comments

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related