parsing user input to Create Filter SQL
-
Hi, I am using a datagrid view to display table data via dataset. additionally I have a number of text and combo boxes where the user can enter filter information so for example the data grid has 2 columns a product and a price. The product filter is a combo box and the the price filter is a textbox. acceptable filters would be for the product: a selected product ( phillips screwdriver) a wildcard entered directely into the combo ( *screwdriver* ) for the price: > 50 And < 300 <= 20 What I am looking for is an easy parsing technique that would convert the user entry into something an SQL where clause (or even a dataset select ) would understand ie Where [Product] = "phillips screwdriver" and [Price] > 50 and [Price] < 300 does anyone have an example algorythim for this kind of thing so that I can build code (or indeed any code to do this would be greatfully received - I can always build an algorythim from it) Thanks Martin
life is a bowl of cherries go on take a byte
-
Hi, I am using a datagrid view to display table data via dataset. additionally I have a number of text and combo boxes where the user can enter filter information so for example the data grid has 2 columns a product and a price. The product filter is a combo box and the the price filter is a textbox. acceptable filters would be for the product: a selected product ( phillips screwdriver) a wildcard entered directely into the combo ( *screwdriver* ) for the price: > 50 And < 300 <= 20 What I am looking for is an easy parsing technique that would convert the user entry into something an SQL where clause (or even a dataset select ) would understand ie Where [Product] = "phillips screwdriver" and [Price] > 50 and [Price] < 300 does anyone have an example algorythim for this kind of thing so that I can build code (or indeed any code to do this would be greatfully received - I can always build an algorythim from it) Thanks Martin
life is a bowl of cherries go on take a byte
This screams one thing, and screams it loud - SQL injection attacks.[^]. You do not want to take user input and directly construct an SQL query from it. What you want to do instead is create an SQL Stored Procedure on the server that will perform your select statement, and pass the user entered values to it. Some info on calling SQL Stored Procedures from VB.NET.[^]
-
This screams one thing, and screams it loud - SQL injection attacks.[^]. You do not want to take user input and directly construct an SQL query from it. What you want to do instead is create an SQL Stored Procedure on the server that will perform your select statement, and pass the user entered values to it. Some info on calling SQL Stored Procedures from VB.NET.[^]
Actually what I want to do is pass a dataset to the GUI holding say Select * from Customers. then use the User input and build a Dataset/DataView Filter on the returned dataset so no SQL injection but its a good warning. thanks also in this app , some data will come from SQL DB and other data from Oracle DB so I have written a middleware layer which will retrieve the data but I dont want to use stored procedures at the DB level when my middleware is currently generic enough to handle both DB's via "standard" SQL. thanks Martin
life is a bowl of cherries go on take a byte