|
Hakmeh Mohannad wrote: right now i dont care about SQL injection all what i want is run this form (Step by Step)
You should know how to prevent them so you can get in the habit if you are going to write code that access databases...
Preventing SQL Injection Attacks[^] is a great article on this site, and it is a fairly easy reading
|
|
|
|
|
There's a bunch of problems here. Where do we start...
First, the connection string shouldn't be passed into functions that use SQL, unless you're doing something very special. A login check doesn't count. Have the connection string retrieved from a Shared method or just get a SqlConnection object from a Shared method. It should get the connection string itself.
Public Class SqlHelpers
Public Shared Function GetSqlConnection() As SqlConnection
Dim connStr As String = GetSqlConnectionString() ' You have to define this method!
Dim newConn As New SqlConnection(connStr)
Return newConn
End Function
Public Shared Function GetSqlConnectionString() As String
' You would normally get the connection string from the app.config file, or some other
' source. With a few changes, this could also support multiple connection strings for
' multiple databases and database types quite easily.
'
' In this example, we'll just return the string you have.
Return "Data Source=HAKMEH;Initial Catalog=HMS;Integrated Security=True;"
End Function
Now, moving on to the actual SQL stuff. You created a DataAdapter and a seperate command, executing the same SQL twice in two different ways. This is, of course, really bad practice. You don't use a DataAdapter for a query like this. All you want is a yes or no answer from the database. You don't retrieve the users ID and password from it!
Also, you made the typical newbie mistake in your SQL by using string concatenation to build your SQL statement, opening yourself to really bad SQL Injection attacks.
Use parameterized queries to block a bunch of injection attacks and it also does the job of escaping special characters for you.
You also didn't validate any of the data comming from the TextBoxes. Rule #1: NEVER trust any data comming from the user.
And you're storing passwords in clear text in the database. If your system is worth securing, secure the passwords from prying eyes, encrypt them! This isn't just a security risk to your app, but to every other system on your network too. People tend to use the same passwords for multiple systems. Don't give anyone a way of easily finding out what that password is and unlocking every system the user has access to.
A better SQL statement would be something like:
SELECT COUNT(UserId) FROM tblEmployees WHERE EmployeeCode=@EmployeeCode AND EmployeePassword=@Password
Note the two parameters with the "@" signs in front of them. They'll get fill in with this:
Public Function Login(ByVal EmployeeCode As String, ByVal EmployeePassword As String) As Boolean
Using conn As SqlConnection = GetSqlConnection()
Dim SelectQuery As String = "SELECT COUNT(UserId) FROM tblEmployees WHERE EmployeeCode=@EmployeeCode AND EmployeePassword=@Password"
Dim comm As New SqlCommand(SelectQuery, conn)
comm.Parameters.Add(New SqlParameter("@EmployeeCode", SqlDbType.NVarChar, 25)).Value = EmployeeCode
comm.Parameters.Add(New SqlParameter("@EmployeePassword", SqlDbType.NVarChar, 25)).Value = EmployeePassword
Dim result As Integer
conn.Open()
result = comm.ExecuteScalar
If result > 1 Then
' Something weird happened. We had more than one user with the same ID in the database!
' Yikes! Someone is going to get shot over this one! :(
' Handle this error however you want.
End If
' If the result came back 1, the login was successfull, otherwise it should be 0.
' So, If result = 1, return True to the caller so it knows the login was successfull.
Return (result = 1)
End Using
End Function
This is by no means a completely secure solution, and is, frankly, "hacked together", but it's stronger than what you had. You would still need to add encryption for the password, making it even stronger. As well as transferring the SQL to an Sql Stored Procedure.
-- modified at 9:04 Wednesday 30th May, 2007
|
|
|
|
|
Dave, that is a brilliantly comprehensive answer for a forum reply. Got my 5!
|
|
|
|
|
Who would have thought such a simple operation would be so complicated.
|
|
|
|
|
Dave Kreskowiak wrote: Who would have thought such a simple operation would be so complicated.
If that is complicated, you should see the multi-threaded invitation-to-renew* plug-in that I'm writing just now for the document delivery system that I wrote last year.
* I work for an insurance company - As policies are about to expire we send documents inviting the policy holder to renew
|
|
|
|
|
Colin Angus Mackay wrote: If that is complicated, you should see the multi-threaded invitation-to-renew* plug-in
No, I just mean from the newbie's point of view. "Bang out a "simple" little login form - no problem!" - yeah right. Until you sit down and think about it. BOOM! Their brains explode forth like biscuit dough out of a can.
I'm quite familiar with the headaches, and rewards, of multithreading. The hard prt is explaining all the pitfalls to someone who has a hell of a time writing a login form!
|
|
|
|
|
Dave Kreskowiak wrote: No, I just mean from the newbie's point of view
Sorry, I mean "you" as in the general "you". English really needs a plural form of you. Like the Scots "youse"
Dave Kreskowiak wrote: BOOM! Their brains explode forth like biscuit dough out of a can.
That's quite an image. I have a very vivid vision of that in my head now.
|
|
|
|
|
Any pointers/samples on how I can pass a string from a Vb.NET dll to a C++ dll and vice versa would be greatly appreciated.
Thank you
|
|
|
|
|
That depends on what the C++ code is expecting, l;ike what type of string and if your expect that string to be modified by the code your calling and how you're calling it. This[^] is about the only guide that covers most of the possiblities.
|
|
|
|
|
Thank you for the reply and link, Dave.
The "C" dll passes a "char*" as input parameter to the VB.NET dll. The VB.NET dll "receives" this input param as a "String" object. This String object is not manipulated/modified by the VB.NET dll - essentially it is a path to a file that it reads and performs a base64 conversion. The base64 converted "String" object is then passed back to the "C" dll.
|
|
|
|
|
Hi Everyone:
I have set up a dataset that uses an auto increment field that is set to start at minus one and step by minus one when I add a new record to the dataset.
Example dataset:
1. Existing Record 1
2. Existing Record 2
3. Existing Record 3
4. Existing Record 4
5. Existing Record 5
The ID field is an auto increment field in SQL Server. To prevent accidental data corruption, whenever I add a new record to the dataset I start with -1. So the dataset will look like this when I add new records:
1. Existing Record 1
2. Existing Record 2
3. Existing Record 3
4. Existing Record 4
5. Existing Record 5
-1. 1st New Record
-2. 2nd New Record
-3. 3rd New Record
Here is my problem when I try to save the new record into the dataset I get the following error:
“Constraint Exception was unhandled. Column ‘ID’ is constrained to be unique. Value ‘4’ is already present.”
I don’t understand why this is happening. The dataset’s auto increment field should put a -1 on the new record I’m adding. Instead it looks like it is starting at the last record ‘5’, subtracts one to get four and is trying to place the new information in record four instead of creating a new record in the dataset and starting the ID number at -1.
What is going on?
Thank you,
Quecumber256
|
|
|
|
|
Actually, it should DBNull. The new record doesn't get an ID until it's written to the database and refetched.
You've been hammering away at this for so long, I have no idea why the ID is so important that it has to remain in order. Databases don't care about the order of records so why are you trying to get these in order?? Only when the records are displayed in some UI do they get any kind of ordering.
|
|
|
|
|
Dave,
I sincerely thank you for your help.
I hope I can explain my problem accurately enough so you can at least get an understanding of what I'm trying to do.
I think I mentioned in a previous post that I have a data entry form that has four text boxes data bound to the BindingSource control. The BingingNavigator control is bound to the BindingSource control to allow the user the ability to navigate through the dataset while it is stored in memory. I designed the form so I can edit existing records as well as add new ones during a session.
The dataset's schema is derived from the table the Dataset is recovering the information from. So in theory I have all four columns ID, Ordinal, Code and name in the dataset.
I'm using the SqlCommandBuilder to automatically generate the add and update queries to update the Database table. Edited records need the ID assigned from the database table before any changes made to them can be written to the database.
The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1 and if you already have a record with the ID of 1 that record will be updated with the new record's data. It thinks you are editing record number one, not adding a new one.
Does this help?
Quecumber256
|
|
|
|
|
Quecumber256 wrote: The new records need to be distingushed from the existing records by setting the ID column in the dataset as a negitively incrementing number. I got this idea from Microsoft MSDN.
No they don't. The records don't exist until they are in the database. Until then, they are potentially records.
Quecumber256 wrote: So in theory if I edit an existing record and then add a new one. The DataSet knows which record was edited by its ID number while the new record is seperately distinguished by its negitive increment.
The DataAdapter, not the DataSet, knows which record was added as opposed to edited by each DataRow's RowState property in each table. The adapter then uses the correct SqlCommand (SQL UPDATE, INSERT, DELETE) to update the database with the new data. It has nothing to do with the ID number you give the record.
Quecumber256 wrote: Unless you set the dataset's autoincrement column to start a -1 and step by -1 whenever you add a new record to the dataset it will start a 1
No, it won't. Any additions to the database are written without an ID number. The database assigns auto-incrementing ID's when the record is commited to the database. You really shouldn't be editing the ID number anyway unless you want to ADD the possibility of duplicate, or incorrect ID's.
|
|
|
|
|
Dave,
I know the DataSet is an Array type object. Whenever data is added to an empty array it starts at 0 and increments by one. So information in a DataSet before it is appended to the database looks like this:
0 Record 1
1 Record 2
2 Record 3
And so on.
The id column in the database will store it in this manner:
1 Record 1
2 Record 2
3 Record 3
and so on.
I must have missed something with the DataAdapter because when I tried to add a new record to the dataset after populating it with data returned from the database I got something like this:
1 <blank>
2 Record 2
3 Record 3
4 Record 4 <the added="" record="">
Can you tell me what I did wrong?
Quecumber256
|
|
|
|
|
Quecumber256 wrote: I know the DataSet is an Array type object.
Uhh, where did you see that?! DataSet implements a collection of DataTable objects as an implementation of the IListSource interface (among others), VERY different from an Array.
Have you just NOT put the ID numbers in??
BTW: A DataSet can't hold DataRows, only a DataTable can. DataSet is a collection of DataTables, which are collections of DataRows.
|
|
|
|
|
Honestly unless this app is going to be run disconnected to the sql server you should probably take a different approach. If you will always be connected to the sql server. I would pass the new record values into a stored procedure that will do the insert. You can have the stored procedure pass back the indentity that it just inserted, or you can just refresh the select query.
If your app can be run dissconnected, then perhaps you should look at adding a large number to your internal dataset, just make sure when the insert happens that it is null so that you get the correct auto increment id from sql server.
Hope that helps.
Ben
|
|
|
|
|
I want the app to run disconnected from the SQL Server database; thus the reason I want to use a DataSet.
I just responded to Dave, I hope the response I gave to him explains what I'm trying to accomplish.
I don't see a reson why I can't distingush a new record added to the dataset by assigning a negitive number to it in the auto increment column while the existing records in the dataset as positive.
Do you have any suggestions on how to approach this problem?
Thanks for you input,
Quecumber256
|
|
|
|
|
Ok now knowing that you want to run this app disconnected. Your idea of the negative auto increment doesn't solve all your problems since I would assume you would have more then one person that would want to run the app disconnect. Now you would have two applications that both assigned -1 to a new row, you would still have the same problem. If you truely want to run the app disconnected, it doesn't matter what the id is in your app when it is disconnected, what matter is when the app syncs with the live database what do you do to get the correct ids. Again I think you are approaching this problem the wrong way. Focus on writing a stored procedure that inserts a new row in to the sql server. Then write your routine to use it when connected and to use some other stored procedure that syncs up the changes that would also use this insert stored procedure.
Ben
|
|
|
|
|
Does anyone know how to insert all items in a listbox into an access database table?
|
|
|
|
|
I'm not on a dev pc at the moment so can't supply any code samples or anything but I'd imagine that you'd probably copy the contents of the listbox to a dataset & then pass the dataset back to the database.
There are other solutions but this is probably one of the easiest. What exactly is your problem?
I'm sooooo happy today!
|
|
|
|
|
I have a program that will allow me to search for files and add the files to a listbox by entering the file folder path in textbox1 and extension in textbox2. I want the files that are in the listbox to be added to an access table called tblRestrFileReport under ExcludeFileName column.
|
|
|
|
|
Then I stand by my OP. Do you know how to work with ADO.NET & datasets? Here is an article on ADO.NET that may prove useful to you.
|
|
|
|
|
how to show video properties.
i use Grapehdit and add filter for capture card,went Right click select filter properties. it show properties...,but i want show properties form in my project,went i click button...
how to coding it
use directshow
thank you
|
|
|
|
|
I have a program that will allow me to enter a folder path into a textbox and file extension into a textbox. Once the path and the extension are typed in, click the search button and the files within the folder specified and the extension entered will be added to listbox1. Also, I have a access table named tblExclude that contains excluded files. In the program, if the folder path in tblExclude is equal to the path entered into the textbox then the filename will be added to listbox2.
For example:
'If the following 2 files are in tblExclude:
ExcludeFilePath: C:WINDOWS\Media
ExcludeFileName: recycle.wav
ExcludeFilePath: C:WINDOWS\Media
ExcludeFileName: recycle2.wav
'Then when you enter your path in Textbox1:
C:WINDOWS\Media
both of the following files that are in tblExclude are added to listbox 2:
recycle.wav
recycle2.wav
But I have a problem. I am only searching for excluded files by the folder path. I need to be able to search for excluded files by the path and extension. Because right now, if I enter folder path "C:\WINDOWS\Media" into textbox1 and click the search button the files in tblExclude will be displayed in listbox 2 without entering the extension in textbox. So I need to be able to search for files in tblExclude by path and ext.
Here is my code:
Dim dt As DataTable<br />
<br />
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click<br />
'Get the entered file paths and patterns<br />
Dim enteredFilePaths As String() = TextBox1.Text.Split(New String() {";"}, StringSplitOptions.RemoveEmptyEntries)<br />
Dim patterns As String() = TextBox2.Text.Split(New String() {";"}, StringSplitOptions.RemoveEmptyEntries)<br />
<br />
'Declare a string array to store the file paths that are not found in the database<br />
Dim filePaths() As String = Nothing<br />
<br />
Dim dv As DataView = dt.DefaultView<br />
dv.Sort = "ExcludePath ASC"<br />
<br />
For Each path As String In enteredFilePaths<br />
'Check to make sure the file path does not have "\" as it's last character.<br />
If path.LastIndexOf("\") = path.Length - 1 Then<br />
path = path.Substring(0, path.Length - 1)<br />
End If<br />
<br />
Dim rows As DataRowView() = dv.FindRows(path)<br />
If rows.Length > 0 Then<br />
For Each row As DataRowView In rows<br />
'It was found in the database, display it in the listbox.<br />
Me.ListBox2.Items.Add(row.Item("ExcludeFileName"))<br />
Next<br />
Else<br />
'It was not found in the database<br />
Dim i2 As Integer = 0<br />
If Not filePaths Is Nothing Then<br />
i2 = UBound(filePaths) + 1<br />
End If<br />
<br />
'Resize the array<br />
ReDim filePaths(i2)<br />
<br />
'Add the path to the array<br />
filePaths(i2) = path<br />
End If<br />
Next<br />
GetDirectoryContents(enteredFilePaths, patterns)<br />
<br />
Private Sub GetDirectoryContents(ByVal dirs() As String, ByVal patterns() As String)<br />
'Declare variable.<br />
Dim dDir As DirectoryInfo<br />
<br />
'Search directory for files and add to the listbox.<br />
For Each sDir As String In dirs<br />
If Not Directory.Exists(sDir) Then Continue For<br />
dDir = New DirectoryInfo(sDir)<br />
For Each ext As String In patterns<br />
For Each fi As FileInfo In dDir.GetFileSystemInfos("*." & ext)<br />
ListBox1.Items.Add(fi.Name)<br />
Next<br />
Next<br />
Next<br />
End Sub<br />
<br />
Private Sub LoadData()<br />
'Create a datatable to store data<br />
<br />
dt = New DataTable("tblExclude")<br />
Dim colExFileID As New DataColumn("ExcludeID")<br />
Dim colExFilePath As New DataColumn("ExcludePath")<br />
Dim colExFileName As New DataColumn("ExcludeFileName")<br />
DataGridView1.DataSource = dt<br />
<br />
dt.Columns.AddRange(New DataColumn() {colExFileID, colExFilePath, colExFileName})<br />
<br />
Dim myConnString As String = "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" & Application.StartupPath & "\File.mdb"<br />
Dim myConnection As New OleDbConnection(myConnString)<br />
myConnection.Open()<br />
Dim strSQL As String = "SELECT * FROM tblExclude"<br />
<br />
Dim dAdapter As New OleDbDataAdapter()<br />
dAdapter.SelectCommand = New OleDbCommand(strSQL, myConnection)<br />
<br />
'Retrieve the data from the database and load the DataTable with it<br />
dAdapter.Fill(dt)<br />
<br />
myConnection.Close()<br />
<br />
End Sub<br />
<br />
Private Sub SearchFileForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br />
LoadData()<br />
<br />
End Sub
Any ideas?
jds1207
|
|
|
|