Results 1 to 11 of 11
-
06-15-2005, 11:30 AM #1Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
Newbie C# Question -- Using Parameters with MySQL INSERT
Hey guys --
Stuck agan! I've been dinking with protecting againts injection thru using parameters, but I can't get it to work how I want. Here's the code...
Code:Cmd.Parameters.Add(new OdbcParameter("sqlListId", "hi")); Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
What am I doing wrong?
-
06-15-2005, 04:40 PM #2WHT Addict
- Join Date
- Aug 2004
- Location
- Tulsa, Oklahoma
- Posts
- 169
Just curious why you have single ticks around your field name and your table name? I don't have anywhere near the experience in MySQL that I do in MS-SQL, so I may be way off.
Michael B
-
06-15-2005, 06:44 PM #3WHT Addict
- Join Date
- Jul 2001
- Location
- Glasgow, Scotland
- Posts
- 130
My C is a bit rusty to say the least, but I'm fairly updated on MySQL, and your query looks a bit weird IMO..
INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId );
I'd try something like;
INSERT INTO customers ('secondary_id') VALUES (@sqlListId);
@sqlListId isn't an array is it? You can only insert one string at a time in one query. Perhaps I'm messing up my C with Perl here, been working way too much in perl lately! >_<
Anyhow, good luckMy development blog - (un)Interesting codesnippets and the occational code-related rant!
-
06-15-2005, 11:17 PM #4Web Hosting Guru
- Join Date
- Aug 2000
- Location
- Redmond, WA
- Posts
- 310
Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:
Code:Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi")); Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
Chris Spangler
chris@thespanglers.net
-
06-16-2005, 02:05 AM #5WHT Addict
- Join Date
- Aug 2004
- Location
- Tulsa, Oklahoma
- Posts
- 169
ahhh good call.
Originally posted by banner
Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:
Code:Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi")); Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
Michael B
-
06-16-2005, 09:35 AM #6Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
Originally posted by banner
Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:
Code:Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi")); Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
Here's the entritey of my SQL code, save the connection string. In actuallity this code is spread out thru-out the name space, but for simplicities sake I'm only posting the relevant information right here.
Code:OdbcConnection _Conn = new OdbcConnection(MyConString); _Conn.Open(); OdbcCommand Cmd = new OdbcCommand("",_Conn); Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi")); Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )"; Cmd.ExecuteNonQuery();
Last edited by FW-Mike; 06-16-2005 at 09:44 AM.
-
06-16-2005, 10:10 AM #7Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
I swear I have tried every possible combination, I've been at this for the last day. I can't get it! Its alwaaayyyss null!
-
06-17-2005, 09:07 AM #8Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
Can anyone clue me in here? I've spent 10+ hours on it.
-
06-17-2005, 10:45 AM #9Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
A little more info on what my situation is, I guess...
The below code DOES WORK. It creates 41 rows each having a secondary ID of TESTER.
Code:string variable = "TESTER"; Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', '"+variable+"', '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')";
Code:string variable = "TESTER"; Cmd.Parameters.Add(new OdbcParameter("@sqlListId", variable)); Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', @sqlListId, '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')";
Code:// Attempt to locate the customer from QB in JTO by secondary ID Cmd.CommandText = "SELECT * FROM `customers` WHERE `secondary_id` = '"+ListID+"' LIMIT 1"; OdbcDataReader MyDataReader = Cmd.ExecuteReader(); // If the customer is found, begin update if(MyDataReader.HasRows) { //do stuff } else { MyDataReader.Close(); string variable = "TESTER"; Cmd.Parameters.Add(new OdbcParameter("@sqlListId", variable)); Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', @sqlListId, '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')"; Cmd.ExecuteNonQuery(); }
Last edited by FW-Mike; 06-17-2005 at 10:56 AM.
-
06-21-2005, 09:19 AM #10Web Hosting Master
- Join Date
- Aug 2002
- Location
- Southwest Michigan
- Posts
- 935
Still need help here..
-
06-22-2005, 11:35 AM #11WHT Addict
- Join Date
- Mar 2004
- Location
- california
- Posts
- 162
Have you tried something like this?
//Test can be any value
string Test = "hi";
//the following code inserts the Test value, replacing a single quote with two single quotes to prevent sql injection
string sql = string.Format("INSERT INTO customers ( secondary_id ) VALUES ( '{0}' )", Test.Replace("'", "''"));
I think that should work, at least in SQL Server. Do you replace single quotes with double quotes? or with a \'? It's been a while since I've worked with mySQL.