Do a Search and populate a grid with results

Question

Hi I have a form that I want to make a search with it. My user have to enter First-Name and/or Last-name to do the search. Once the search is done I would like to populate my datagrid. I tried different method but I had problem with the Single quote (sql injection) on my query. Does anybody have a good idea how I could do the search and put it in the datagrid please? I'm using Ado.net with a dataset. If you need further informations please ask me. Thanks in advance

miker · Answer

Sorry if I sounded sarcastic. I really appreciate the example you posted. It helped me to understand something i did not think about. BTW there is no table Users in my database. :))

mgiroux · Answer

I feel a little bit of sarcasism here. This article tell how to replace the Sql Injection problem in vb. The commands are not the same that I want but the important thing is not there. They say that you should use parameters instead of the double quote to replace the single quote. Anyway I know how to do my search. The only thing that is wrong is the replace(...) part because of the reasons that this person wrote to this article. I just wanted to know if it have another way instead of using parameters to do want I want to do. If you do a search on the SqlInjection on the Microsoft Site and you'll find something as I didnt' invent it. Certainly I could let my code as it is. But I do think that security is the most important thing in an application. I think that if is it not secure at 100% it is not a good application... Another thing I knew that you didnt' want to say that the user enter two single quotes instead of one. I knew you were talking about the code. I already did it before you told me and that's where people told me that I shouldn't use it for security reasons. (the replace part) And if you don't have user that can guess that your table Users is called Users I do! So I can't take the chance to let it that way. I'm sorry for all the confusion that I created on this thread and I want to say thank you to Mike to took time to try to help me. But it seems like I won't find here what I am searching for. Thanks anyway.

miker · Answer

Good article! Even it does not have anything to do with your original question. However none of the command listed in the article will work on my database. Simply because of security settings. Executing something like 'DROP TABLE' or 'EXEC xp_cmdshell' requires specific privileges and I'm not granting them to users. Of course it's still possible that user will try to run something like 'DELETE * FROM mytable'. But to do so users have to know stucture of the database and they are not. Ok they can guess and the danger still there. And it's my responsibility to minimize such danger. Going back to your original questions. I did not mean that user have to enter 2 single quotes instead of one. I meant that your code should check user input and insert second quote when needed.

mgiroux · Answer

Sorry for the long post I could post the link but I guess that the administrator won't like a link to another forum of Vb.net... Use ADO parameters. They take care of this problem plus the related problem with using the double-quote character. They're a little more effort to code but if you're doing multiple inserts into the same table or multiple selects with different value crtieria they end up running faster than concatenating a SQL string together. Dim adoConn as ADODB.Connection Dim adoComm as ADODB.Command Dim adoParamUserName as ADODB.Parameter Dim adoParamPWD as ADODB.Parameter Dim sSQL as String Dim i as Long sSQL =  sSQL = sSQL &  INSERT INTO tbl_User sSQL = sSQL &  (UserName Password) sSQL = sSQL &  VALUES (? ?) Set adoConn = New ADODB.Connection adoConn.ConnectionString = My Connection String adoConn.Open Set adoComm = New ADODB.Command adoComm.ActiveConnection = adoConn adoComm.CommandText = sSQL Set adoParamUserName = adoComm.CreateParameter(UserName adString adParamInput) Set adoParamPWD = adoComm.CreateParameter(Password adString adParamInput) adoComm.Parameters.Add adoParamUserName adoComm.Parameters.Add adoParamPWD For i = 1 to NumUsers adoParamUser.Value = User(i) adoParamPWD.Value = PWD(i) adoComm.Execute   adExecuteNoRecords Next i Set adoParamUser = Nothing Set adoParamPWD = Nothing Set adoComm = Nothing If Not adoConn Is Nothing Then If adoConn.State = adStateOpen Then adoConn.Close End If Set adoConn = Nothing End If Hope this helps. =============================== Update: 2003-09-09 Since I wrote this FAQ many years ago I've learned more about why using ADO parameters is a good idea. Primarily the best reason to use parameters is to protect yourself against a cracking attack known as SQL Injection. This happens because the programmer used string concatenation to build their SQL. If your SQL looks like this: sSQL =  sSQL = sSQL &  INSERT INTO tbl_User sSQL = sSQL &  (UserName Password) sSQL = sSQL &  VALUES (' & sUsername & ' ' & sPassword & ') you are vulnerable to this attack. What happens is the attacker instead of using a valid username or password uses a value that looks like this: ';DROP TABLE tbl_User -- so that when it gets seen by the database it looks like this: INSERT INTO tbl_User (UserName Password) VALUES ('';DROP TABLE tbl_User; --' 'Mypasswordvalue') If you analyze what happens here the SQL execution engine says fine..fine..Oh a semicolon. The semicolon terminates the first part of the statement and it gets executed. It will error out but because there is still more to the statement to do it keeps going. It then says Oh I need to drop table user. Which it does (uh-oh!). The rest of the statement is ignored because the double-dash is SQL's comment indicator. So guess what has happened -- instead of adding a user to your table your attacker has instead dropped all users from your database. Do you have a backup? I hope so because they're all gone now. It gets worse. What if the attacker were to send you a string that looks like this:';EXEC xp_cmdshell Format C: -- When the SQL engine runs this it will execute the xp_cmdshell stored procedure. And the parameter that gets passed to it will format your C: drive. I hope you really have a backup as your operating system just got wiped. Putting your data files on drive D: is of no help as the attacker might send multiple requests starting at drive Z: and working his way down. I hope you didn't have any drives mapped to other machines! Using ADO parameters prevents all this as they will filter out these sorts of attacks. Yes it's more work to code. But having to recover from one of these attacks is even less fun. BTW: Don't forget that the attack can come from more than just your GUI - it can come from a Web form an imported XML file or a web service. Basic rule is: DO NOT TRUST DATA FROM THE USER.

miker · Answer

Security problem? Why?

mgiroux · Answer

I did this at the beginning but some people told me that it wasn't a good way to proceed as I could have security problem. I tried to search on the Sql Injection but I didn't find what I needed...

miker · Answer

Make it 2 single quotes.