|
|
You're in the
vb.net data
group:validate SQL-query
vb.net data:
handle all single quote issues--if you use it. Instead of passing raw SQL to the engine, pass a parameterized query. The provider is responsible for framing the strings properly. -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Erik van Engelen" <info@kkoerhuis.nl> wrote in message news:46965592$0$334$e4fe514c@news.xs4all.nl... > Is it possible to validate or format a dynamic SQL-query in vb.net? > > I have an application that passes data from a form to a MySQL database. > This data can include all kinds of user passed data like single quotes or > reserved words. Before creating the query i want to format the data with a > function. > > I've worked with vb6 for 6 years but .net is new to me. > > Thanks.
Is it possible to validate or format a dynamic SQL-query in vb.net?
I have an application that passes data from a form to a MySQL database. This data can include all kinds of user passed data like single quotes or reserved words. Before creating the query i want to format the data with a function. I've worked with vb6 for 6 years but .net is new to me.
[quoted text, click to view]
> Again, it's the job of the provider's (MySQL's data access interface) to > handle all single quote issues--if you use it. Instead of passing raw SQL > to the engine, pass a parameterized query. The provider is responsible for > framing the strings properly. I'm new to .NET too. I think what Bill is saying is: instead of using: sql = "UPDATE table SET field='" & TextBox.Text & "'" cmd.ExecuteNonQuery(sql) you should use something like : sql = "UPDATE table SET field=@Field" cmd.Parameters.Add("@Field", OleDBType.VarChar).Value=TextBox.Text cmd.ExecuteNonQuery(sql) Is that right?
Right. ;)
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Phil" <N/A> wrote in message
news:46974452$0$1606$ed2619ec@ptn-nntp-reader02.plus.net... >> Again, it's the job of the provider's (MySQL's data access interface) to >> handle all single quote issues--if you use it. Instead of passing raw SQL >> to the engine, pass a parameterized query. The provider is responsible >> for framing the strings properly. > > I'm new to .NET too. I think what Bill is saying is: > instead of using: > > sql = "UPDATE table SET field='" & TextBox.Text & "'" > cmd.ExecuteNonQuery(sql) > > you should use something like : > > sql = "UPDATE table SET field=@Field" > cmd.Parameters.Add("@Field", OleDBType.VarChar).Value=TextBox.Text > cmd.ExecuteNonQuery(sql) > > Is that right? >
On Mon, 30 Jul 2007 16:06:39 +0200, Erik van Engelen
[quoted text, click to view] <info@kkoerhuis.nl> wrote: >Phil wrote: >>> Again, it's the job of the provider's (MySQL's data access interface) to >>> handle all single quote issues--if you use it. Instead of passing raw SQL >>> to the engine, pass a parameterized query. The provider is responsible for >>> framing the strings properly. >> >> I'm new to .NET too. I think what Bill is saying is: >> instead of using: >> >> sql = "UPDATE table SET field='" & TextBox.Text & "'" >> cmd.ExecuteNonQuery(sql) >> >> you should use something like : >> >> sql = "UPDATE table SET field=@Field" >> cmd.Parameters.Add("@Field", OleDBType.VarChar).Value=TextBox.Text >> cmd.ExecuteNonQuery(sql) >> >> Is that right? >> > >Sadly this doesn't work. I have the following code: > >Dim dbParam As Data.Common.DbParameter >Command_DBt = Dbt.CreateCommand 'Dbt is the target mysql-ODBC connection) > >strSQLCommand = "INSERT INTO afwijking (`afwijkid`, `grstid`, `dgrpid`, >`afwijking`) VALUES (@field0,@field1,@field2,@field3);" > > Command_DBt.CommandText = strSQLCommand > Do While Reader_DBs.Read > For tel_fields = 0 To maxcount_fields - 1 > > dbParam = Command_DBt.CreateParameter > dbParam.DbType = DbType.Int64 > dbParam.ParameterName = "@field" & tel_fields.ToString > dbParam.Value = Reader_DBs.GetValue(tel_fields) > Command_DBDoel.Parameters.Add(dbParam) > Next (tel_fields) > > Command_DBDoel.ExecuteNonQuery() > Command_DBDoel.Parameters.Clear() > Loop > >This is probably not to most efficient code but it should work. When i >look in the mysql query logs i get "INSERT INTO afwijking >(`afwijkid`,`grstid`,`dgrpid`,`afwijking`) VALUES >(@field0,@field1,@field2,@field3)". This means the parameters are not >replaced by the values. The MS-help is not very helpful in giving >examples or explanations. I've been googling for a day now but i can't >seem to find the problem. > >Does anyone have a solution? Depending on which provider you are using, you may need to use '?'
[quoted text, click to view]
Phil wrote: >> Again, it's the job of the provider's (MySQL's data access interface) to >> handle all single quote issues--if you use it. Instead of passing raw SQL >> to the engine, pass a parameterized query. The provider is responsible for >> framing the strings properly. > > I'm new to .NET too. I think what Bill is saying is: > instead of using: > > sql = "UPDATE table SET field='" & TextBox.Text & "'" > cmd.ExecuteNonQuery(sql) > > you should use something like : > > sql = "UPDATE table SET field=@Field" > cmd.Parameters.Add("@Field", OleDBType.VarChar).Value=TextBox.Text > cmd.ExecuteNonQuery(sql) > > Is that right? > Sadly this doesn't work. I have the following code: Dim dbParam As Data.Common.DbParameter Command_DBt = Dbt.CreateCommand 'Dbt is the target mysql-ODBC connection) strSQLCommand = "INSERT INTO afwijking (`afwijkid`, `grstid`, `dgrpid`, `afwijking`) VALUES (@field0,@field1,@field2,@field3);" Command_DBt.CommandText = strSQLCommand Do While Reader_DBs.Read For tel_fields = 0 To maxcount_fields - 1 dbParam = Command_DBt.CreateParameter dbParam.DbType = DbType.Int64 dbParam.ParameterName = "@field" & tel_fields.ToString dbParam.Value = Reader_DBs.GetValue(tel_fields) Command_DBDoel.Parameters.Add(dbParam) Next (tel_fields) Command_DBDoel.ExecuteNonQuery() Command_DBDoel.Parameters.Clear() Loop This is probably not to most efficient code but it should work. When i look in the mysql query logs i get "INSERT INTO afwijking (`afwijkid`,`grstid`,`dgrpid`,`afwijking`) VALUES (@field0,@field1,@field2,@field3)". This means the parameters are not replaced by the values. The MS-help is not very helpful in giving examples or explanations. I've been googling for a day now but i can't seem to find the problem.
Jack is 100% right. OLE DB generally does not support named parameters
except for recent SQL Server providers--you'll have to use the appropriate parameter markers like "?" or ":" (for Oracle). hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Jack Jackson" <jacknospam@pebbleridge.com> wrote in message
news:73vra3prhf518fii29hn16r9cl5fgjhj0l@4ax.com... > On Mon, 30 Jul 2007 16:06:39 +0200, Erik van Engelen > <info@kkoerhuis.nl> wrote: > >>Phil wrote: >>>> Again, it's the job of the provider's (MySQL's data access interface) >>>> to >>>> handle all single quote issues--if you use it. Instead of passing raw >>>> SQL >>>> to the engine, pass a parameterized query. The provider is responsible >>>> for >>>> framing the strings properly. >>> >>> I'm new to .NET too. I think what Bill is saying is: >>> instead of using: >>> >>> sql = "UPDATE table SET field='" & TextBox.Text & "'" >>> cmd.ExecuteNonQuery(sql) >>> >>> you should use something like : >>> >>> sql = "UPDATE table SET field=@Field" >>> cmd.Parameters.Add("@Field", OleDBType.VarChar).Value=TextBox.Text >>> cmd.ExecuteNonQuery(sql) >>> >>> Is that right? >>> >> >>Sadly this doesn't work. I have the following code: >> >>Dim dbParam As Data.Common.DbParameter >>Command_DBt = Dbt.CreateCommand 'Dbt is the target mysql-ODBC connection) >> >>strSQLCommand = "INSERT INTO afwijking (`afwijkid`, `grstid`, `dgrpid`, >>`afwijking`) VALUES (@field0,@field1,@field2,@field3);" >> >> Command_DBt.CommandText = strSQLCommand >> Do While Reader_DBs.Read >> For tel_fields = 0 To maxcount_fields - 1 >> >> dbParam = Command_DBt.CreateParameter >> dbParam.DbType = DbType.Int64 >> dbParam.ParameterName = "@field" & tel_fields.ToString >> dbParam.Value = Reader_DBs.GetValue(tel_fields) >> Command_DBDoel.Parameters.Add(dbParam) >> Next (tel_fields) >> >> Command_DBDoel.ExecuteNonQuery() >> Command_DBDoel.Parameters.Clear() >> Loop >> >>This is probably not to most efficient code but it should work. When i >>look in the mysql query logs i get "INSERT INTO afwijking >>(`afwijkid`,`grstid`,`dgrpid`,`afwijking`) VALUES >>(@field0,@field1,@field2,@field3)". This means the parameters are not >>replaced by the values. The MS-help is not very helpful in giving >>examples or explanations. I've been googling for a day now but i can't >>seem to find the problem. >> >>Does anyone have a solution? > > Depending on which provider you are using, you may need to use '?' > instead of '@'.
[quoted text, click to view] "William (Bill) Vaughn" <billvaRemoveThis@betav.com> wrote in message news:25D49201-3C85-4E87-A509-E4BAC90C39A1@microsoft.com... > Jack is 100% right. OLE DB generally does not support named parameters > except for recent SQL Server providers--you'll have to use the appropriate > parameter markers like "?" or ":" (for Oracle). I've been using named parameters with Access/Jet & OleDb with no problems. I've not tried it with Oracle though.
Don't see what you're looking for? Try a search.
|
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |