|
I want to create an SQL syntax using VB.Net which will allow me to filter data on a date field in my table in MySql with the current Date. The issue is I have the dates saved in the Table with Type VARCHAR(). One way is to convert the current Date to String and then compare. But is there any process by which I can convert the Table String field to Date format and then compare with current Date. Basically my SQL Syntax with be something like below:
strSql = "Select * from TBL1 where TBL_Date1 >= " + Now()
This will not work since TBL_DATE1 is a VARCHAR Type and NOW() is of Type DateTime
I am pretty new to .net and hence would like some help from you all.
|
|
|
|
|
It's a really bad idea to store dates as VARCHAR in your database, you should use the DATETIME type. However, you can convert it to a date value as described here[^].
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
SPSandy wrote: This will not work since TBL_DATE1 is a VARCHAR Type
- Dates are stored as a date, not as a varchar. That's what needs to be changed, not the query; converting the varchar to a date to do a comparison is a dirty hack, trying to work around a previous mistake. (If you move the database or the culture changes, things will break.)
- It's recommended to sanitize the query; use a DbParameter, the way it's now I could destroy your database.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
All your database are belong to us ?
My advice is free, and you may get what you paid for.
|
|
|
|
|
I understand your advice and would like to go in the right way. But the problem is being new to VB.Net I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date before storing by using Format(Date.Parse(txtdt.Text), "yyyy/mm/dd"). But this is not working. I have been trying to find some solution from internet but not able to get anything. This prompted me to move ahead for the time being by using VarChar. But now I am stuck since comparison in VarChar does not give the right result
|
|
|
|
|
SPSandy wrote: But the problem is being new to VB.Net
..that's not a problem, just a matter of time. Next year you'll be explaining the concept to someone else who is new
SPSandy wrote: I am not able to save the date Value into MySql from a Text Box. Since MySql uses the format yyyy/mm/dd I have tried to convert my date
Databases do not store date's in a specific notation, they store it as a number. Internally, it is just "n" numbers that have passed since januari 1st, 1900, (or some other date) with the time-part stored as a fraction. (Imagine the double 3693.5; that'd be 3693 days since the epoch, and .5 as the time, so probably 12:00 AM)
When you "ask" MySql for a date, it returns a Date type, a double - not a text with month-separators as a string. The problem with using a string, is that the datetime-functions do not know how to interpret it - they see a string, a text, not a datetime.
When you get a value from a textbox, that datetime is in the "users" locale; it's in a specific format, perhaps with daylight saving. You convert that to a "real" DateTime and store it in it's native format. (Otherwise we'd have to teach all the date-functions the differences between timezones and teach them how to read a date in a foreign notation!)
To sum it up; if you have the option of converting the field back to a DateTime, please do so; it'll save you from a lot of headaches in the future. Yes, I can see how it is a workaround for the problem you described, but the price for the trade-off is too high. If you're having trouble making it work, post the code and we'll have a look.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for the comment. I have finally been able to solve the issue. MySql does accept only yyyy/mm/dd when saving data. In case your data is in any other format then it will display an error.
|
|
|
|
|
i need a dll or somthing to be integrated in my application to convert a given file fron VB.NET code to C# code like , can anyone help?
|
|
|
|
|
Member 9491102 wrote: dll or somthing
Why? Why don't you just translate the VB to C# with an already existing converter?
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012
|
|
|
|
|
In midi form (of VB6.0) no attributes or methods:
MidiForm.BorderStyle = 0
MidiForm.AutoRedraw = True
MidiForm.Refresh
MidiForm.Cls
other form, you can add properties or methods on the midi form?
|
|
|
|
|
you can use the tag proprety
or muts better: switch to dotnet
|
|
|
|
|
I have not used the tag proprety, so I do not know how to use, you can for example illustrate the use proprety tag, if you share yourself with, I do need them. thanks
|
|
|
|
|
Member 2458467 wrote: other form, you can add properties or methods on the midi form?
Midi? Or an MDI-Form?
Member 2458467 wrote: of VB6.0
You DO know that VB6 has been phased out? That you cannot buy a new copy of the IDE? And that there will be less support for the platform every year? If you're writing new code in VB6, you might as well write the comments in Latin.
As Jan said, install .NET, and create the form in there. You can hook it into your existing application, so you don't have to rewrite the whole thing at once. Start here[^].
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I know .NET support is very good for programming, but I am very need for example can add properties or methods to the MDI-Form of VB6.0. I want to know how computers work like ?
|
|
|
|
|
Member 2458467 wrote: I know .NET support is very good for programming
That's not what I said. I said that VB6 is dead. Buried. No longer supported. VB's not pinin'! VB's passed on! This language is no more! It has ceased to be! VB's expired and gone to meet 'is maker! VB's a stiff! Bereft of life, it rests in peace! If you hadn't nailed 'im to the perch 'e'd be pushing up the daisies! 'Is metabolic processes are now 'istory! VB's off the twig! VB's kicked the bucket, it's shuffled off 'is mortal coil, run down the curtain and joined the bleedin' choir invisibile!! THIS IS AN EX-LANGUAGE!!
To sum it up; you don't write any new code in VB6.
Member 2458467 wrote: for example can add properties or methods to the MDI-Form of VB6.0
As far as I'm concerned, this is done using .NET. I too, have stopped supporting the dead language.
Member 2458467 wrote: I want to know how computers work like ?
"Reliable". Wrong forum, wrong question.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I've been asked to design a contact manager-like application for a friend's business. The application will have multiple user logins and all information is stored in a database (MySQL). Some information that will need to be stored needs to be encrypted because of the sensitivity of it; like credit card information. I'm familiar with using encryption in .NET. However there is one thing which I know how to do, but I'm trying to find the most secure way of doing it:
Normally if an application only has one user, or if fields of a record are tied to only one specific user (in a multi-user system), I could use that users password to encrypt the customer's information in the database (and that way the password wouldn't need to be stored anywhere). In this case though, that won't work. This is because multiple user's may need access to this information; say customer services rep. (CSR) A on Monday enters a customer's information and it is encrypted with CRS A's password. Then on Tuesday CSR B needs to access the same customer's information, they wouldn't be able to because the information wasn't encrypted with CSR B's password.
In a case like this, the application needs some constant password so that encrypted fields that need to be accessed by different people can be. Of course I could always just create a constant like Private Const Key As Byte() = {198, 167, 93, 121, 252, 215} . However I would think there would be a better way where it isn't just plain to see and can't be so easily found using ildasm.exe.
At first I was thinking of using some computer unique piece of information, but then the information wouldn't be accessible from multiple computers.
I know nothing is always 100% secure, and I'm not expecting that; just a better way of doing this to improve the quality and security of this application, and others I create in the future. I may be over-thinking this, like I usually do, but if anybody has any suggestions, or links to articles, that describe a good way to store a system-wide password like this I would greatly appreciate it.
Thanks in advance.
|
|
|
|
|
Why not just allocate user access classes to privileged users, such that when they logon successfully using their own password, they are allocated an access token which gives them the ability to read and/or update certain information.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
This might be interesting to you ...
http://www.rsa.com/products/bsafe/whitepapers/DDES_WP_0702.pdf[^]
Since you already know .Net encryption, I would build my encryption routines into my data-access layer; storing encrypted data in the database. To keep my sanity, I wouldn't encrypt everything just sensitive stuff.
|
|
|
|
|
@Richard: Not sure what you mean by access token. If the access token is used as an encryption key, then it would need to be the same for everyone all the time, ergo it would need to be saved as a constant somewhere. I can't keep all the database fields as plain text and limit access solely based on user authentication because of legal reasons; certain fields HAVE to be encrypted.
@David: I took a look at that PDF you provided and it describes using an encryption server separate from the database server. I just want to make sure I understand this concept.
On the encryption server there is a database that store say my customer's ID numbers and their encryption key. When my customer's employee starts up the application and logs in, the application connects to the encryption server over HTTPS (or other secure connection) and get the encryption key which is then used to decrypt my customer's client's information in the database? I drew a diagram of how I'm picturing it in my head located here. Is this correct? If so, as long as the request/retrieval of the encryption key is done over HTTPS then it should be secure, right?
|
|
|
|
|
DisIsHoody wrote: If the access token is used as an encryption key No, the access token tells you which information a specific user is allowed to access. This does not prevent you from encrypting everything, but since encryption can only use a single key this is one way to restrict the information available to different users.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
One way of achieving an encryption that multiple users can use is follows
The system contains the following
PlainText
CipherText
EncryptionKeyShared -- Decrypts above ciphertext
EncryptionKeyUserA -- Encryption key for user A
EncryptionKeyUserB -- Encryption key for user B
First, randomly generate a new EncryptionKeyShared
Then encrypt the plaintext with EncryptionKeyShared
Then encrypt the EncryptionKeyShared with EncryptionKeyUserA gives SecretKeyA
Then encrypt the EncryptionKeyShared with EncryptionKeyUserB gives SecretKeyB
Store the row like this
CiperText, SecretKeyA, SecretKeyB, ...SecretKeyZ
Now to descrypt, user A puts in their password, descrypts SecretKeyA using their password
This gives UserA access to EncrytpionKeyShared which can now be used to decrypt CipherText back to PlainText
Cheers
Matt
|
|
|
|
|
I've never seen this solution before but I like it. One question though. If for example someone got a hold of all the SecretKey(x), would it be easier to determine the EncryptionKeyShared if they knew which algorithm was used (like with some sort of collision search) or does the fact that EncryptionKeyShared was encrypted multiple times with different EncryptionKeyUser(x) not make any difference?
|
|
|
|
|
I don't believe this represents any risk. The types of attacks your talking bout usually require hundreds gigabytes or even thousands of terabytes of collected data to reveal a key.
In addition, if EncryptionKeyShared is random they've no way of knowing if they are getting closer or not.
The method I described is basically how windows manages encryption keys for NTFS encrypted files.
The important part of keeping this secure is make sure that EncryptionkeyShared is created with a cryptographically strong random number generator and is never sent to the client, eg, only the server should make use of EncryptionKeyShared, it should never go to the client.
Cheers
Matt
|
|
|
|
|
Oh -- one thing I forgot, for this method to work, when a user changes their password. You'll also have to encrypt all their EncryptionKeyShared(s) under the new password
Possibly you could just use one EncryptionKeyShared for all data to make this more convenient.
|
|
|
|
|
Matty22 wrote: only the server should make use of EncryptionKeyShared
Now I'm a little lost on how I could implement this. Currently the server is just housing the database with customer information. Each employee will have a computer at their desk which contains the application. When they log in, their password is hashed and compared against the saved hash in on of the tables.
Once they are logged in they will be able to look up records by typing in say a project number. This information will be retrieved from the database either by inline SQL statements or by stored procedures. Either way, if a column has encrypted data in it, it will be transmitted from the database server to the employees application and then be decrypted by the employee's application. For this to happen, the employee's application will need to make use of the EncryptionKeyShared.
The only way to make sure that only the server is making use of the EncryptionKeyShared is to write a server application, or service, that would accept request from the employee's application, retrieve the requested database application, decrypt the necessary columns, and finally transmit the data to the employee's application.
Two complications I see with this is that 1. a server application needs to be created to handle the incoming request from all the employee's computers and synchronize them so there isn't any conflict between data request or race conditions and 2. while the database information is being sent from the database server to the employee's computer the information is not encrypted.
If the database is on a local server then Item 2 may not be much of an issue, but if it is decided to keep the database server off-site, then it seems like have the data encrypted while it is being transmitted from the server to the employee's computer is a much better option then having it in plain text (even if it is going over a HTTPS connection).
Still, Item 1 still make the whole application more complicated; unless I'm missing something where a server application/service doesn't need to be created to implement the EncryptionKeyShared as you have described.
|
|
|
|
|