Convert input explicitly at your client; don’t rely on the database to “figure it out”
A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc). I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled.
In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values ('" + txtDate.Text + "')";
c.ExecuteNonQuery();
Now, I think that hopefully even most beginners will agree that this is bad code. The primary issue, of course, is SQL Injection. Avoiding SQL Injection is very easy to do using Parameters. So, let's say that you rewrite this code using parameters like this:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.AddWithValue("@DateVal",txtDate.Text);
c.ExecuteNonQuery();
Looking at that, it seems we have done quite a bit better and should be happy with the code. It works well, and no injection is possible. But there is still an issue! Why? The txtDate.Text property returns a string, not a DateTime! And, since we are not setting the data type of the parameter explicitly, the parameter being passed is a string (i.e., VARCHAR or NVARCHAR) value, not a true DateTime value. This means that SQL Server must implicitly cast your string to a DateTime to store it in your table, and this may or may not work successfully, or as expected, depending on how the string is formatted.
I've said it over and over and I'll say it again: The concept of formatting dates should never be something that your database code should ever worry about. The database layer should be accepting DateTime data from clients, and returning DateTime data to your clients. Where and how the client got the data before passing it to the database, or what the client does with the data in terms of formatting after receiving it from the database is of no concern to the database itself.
So, we might decide that to fix this, we can simply declare the data type of the parameter explicitly:
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = txtDate.Text;
c.ExecuteNonQuery();
It appears that now we are in good shape, right? Actually -- no! There is still an implicit conversion happening, because we are still passing a string value -- the txtDate.Text property -- to the parameter, not a true DateTime!
Let's try one more time. How can we avoid these implicit conversions? The answer that question is always the same: Convert explicitly! Your client application is fully capable of handling the parsing, validation, and conversion of that string to a true DateTime value, so go ahead and do it:
DateTime dateval = DateTime.Parse(txtDate.Text); // plus more code to validate, of course
SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = dateval;
c.ExecuteNonQuery();
Now we are in business! Before we even create the SqlCommand object, we have a true DateTime value that we are ready to pass along to SQL Server. Our SQL code doesn't need to worry about formatting, parsing, converting, or anything -- it is being passed a completely valid piece of data with the correct type. In short, we can now be sure that whatever value we came up with for the date in our client code is exactly the value that will be stored in our database. That's the idea, right?
So, please, don't rely on your database code to validate your input. Don't just pass along generic string data and "hope" that at the end of the day the database can "handle it". Eliminate the chance of anything going wrong and write your code to explicitly cast and convert and validate any and all input before the database even comes into the picture.
Hello! Please e-mail me your contacts. I have a question webmaster@complective.ru” rel=”nofollow”>……
Thank you!!!…
Buy:Viagra Super Active+.Zithromax.Cialis Professional.Soma.Tramadol.Cialis Soft Tabs.Viagra.Maxaman.VPXL.Cialis.Cialis Super Active+.Propecia.Super Active ED Pack.Levitra.Viagra Soft Tabs.Viagra Professional.Viagra Super Force….
Buy:VPXL.Cialis Super Active+.Zithromax.Levitra.Tramadol.Viagra Soft Tabs.Maxaman.Cialis Soft Tabs.Viagra Professional.Propecia.Cialis.Viagra Super Active+.Soma.Cialis Professional.Viagra.Super Active ED Pack.Viagra Super Force….
Buy:Arimidex.Synthroid.Petcam (Metacam) Oral Suspension.Nexium.Prevacid.Zovirax.Zyban.100% Pure Okinawan Coral Calcium.Actos.Valtrex.Lumigan.Prednisolone.Human Growth Hormone.Retin-A.Mega Hoodia.Accutane….
Buy:Female Cialis.Advair.Amoxicillin.Lipothin.Ventolin.Zocor.Buspar.SleepWell.Acomplia.Female Pink Viagra.Cozaar.Aricept.Zetia.Nymphomax.Seroquel.Benicar.Prozac.Lasix.Lipitor.Wellbutrin SR….
…
BUY FASHION. TOP BRANDS: GUCCI, DOLCE&GABBANA, BURBERRY, DIESEL, ICEBERG, ROBERTO CAVALLI, EMPORIO ARMANI, VERSACE…
Buy@Cheap.Abana” rel=”nofollow”>..…
Buygeneric pills…
Purchase@Discount.Abana” rel=”nofollow”>……
Buywithout prescription…
Cheap@Abana.Online” rel=”nofollow”>..…
Buynow it…
Buy@Generic.Abilify.5mg.10mg.15mg.20mg.30mg” rel=”nofollow”>……
Buywithout prescription…
Buy@Acai.Online” rel=”nofollow”>.…
Buywithout prescription…
Purchase@Discount.Acai” rel=”nofollow”>..…
Buywithout prescription…
Buy@Acai.Without.Prescription” rel=”nofollow”>……
Buynow…
Purchase@Generic.Acai.500mg” rel=”nofollow”>..…
Buyno prescription…
Buy@Cheap.Coral.Calcium” rel=”nofollow”>.…
Buynow it…
Buy@Cheap.Coral.Calcium” rel=”nofollow”>……
Buygeneric meds…
coral@calcium.is.it.safe” rel=”nofollow”>..…
Buyit now…
Cheap@Coral.Calcium.Online” rel=”nofollow”>..…
Buygeneric drugs…
Order@Discount.Abana” rel=”nofollow”>..…
Buygeneric meds…
Purchase@Coral.Calcium.Online” rel=”nofollow”>..…
Buygeneric meds…
Purchase@Discount.Coral.Calcium” rel=”nofollow”>……
Buygeneric pills…
Buy@Cheap.Abilify” rel=”nofollow”>……
Buygeneric pills…
Buy@Cheap.Acai” rel=”nofollow”>……
Buywithout prescription…
Buy@Discount.Acai” rel=”nofollow”>……
Buygeneric drugs…
Order@Accupril.Online” rel=”nofollow”>.…
Buygeneric drugs…
Buy@Discount.Accutane” rel=”nofollow”>.…
Buynow it…
Purchase@Cheap.Accutane” rel=”nofollow”>.…
Buynow it…
.…
Buygeneric drugs…
Get@Aciphex.Online” rel=”nofollow”>.…
Buygeneric pills…
Cheap@Generic.Actonel.35mg” rel=”nofollow”>..…
Buygeneric meds qmo…
Buy@Discount.Actoplus.Met” rel=”nofollow”>..…
Buygeneric pills zdg…
Purchase@Discount.Actoplus.Met” rel=”nofollow”>..…
Buygeneric drugs krg…
Get@Actos.Online” rel=”nofollow”>..…
Buywithout prescription dyo…
Buy@Generic.Actos” rel=”nofollow”>..…
Buynow it vhx…
Order@Discount.Acular” rel=”nofollow”>.…
Buygeneric drugs zfk…
Buy@Cheap.Coral.Calcium” rel=”nofollow”>……
Buywithout prescription xiu…
Buy@Discount.Acai” rel=”nofollow”>.…
Buygeneric drugs axm…
Order@Discount.Acai” rel=”nofollow”>.…
Buygeneric drugs nih…
Order@Accupril.Online” rel=”nofollow”>..…
Buyno prescription tao…
Purchase@Accupril.Online” rel=”nofollow”>.…
Buywithout prescription yyn…
Cheap@Accupril.Online” rel=”nofollow”>..…
Buygeneric drugs ajd…
Buy@Accutane.Online” rel=”nofollow”>..…
Buyno prescription jui…
Order@Accutane.Without.Prescription” rel=”nofollow”>..…
Buyno prescription gdt…
Purchase@Accutane.Without.Prescription” rel=”nofollow”>.…
Buyit now ojb…
Purchase@Generic.Accutane.10mg.20mg” rel=”nofollow”>.…
Buygeneric drugs itj…
Order@Aciphex.Online” rel=”nofollow”>.…
Buynow it lmg…
Order@Discount.Aciphex” rel=”nofollow”>……
Buygeneric meds svb…
Purchase@Discount.Acomplia” rel=”nofollow”>..…
Buynow it xmt…
Order@Generic.Actonel” rel=”nofollow”>.…
Buydrugs without prescription xab…
Cheap@Advair.Without.Prescription” rel=”nofollow”>……
Buyit now sge…
Buy@Cheap.Aggrenox” rel=”nofollow”>..…
Buygeneric meds dxy…
Buy@Cheap.Aldactone” rel=”nofollow”>..…
Buydrugs without prescription hak…
hair@growth.and.accutane” rel=”nofollow”>..…
Buygeneric drugs…
can@you.take.aciphex.and.nexium.at.the.same.time” rel=”nofollow”>..…
Buygeneric pills…
actonel@and.heart.irregularities” rel=”nofollow”>..…
Buyno prescription…
aldactone@kidney.problems” rel=”nofollow”>……
Buygeneric drugs…
altace@cough.buy” rel=”nofollow”>..…
Buynow…
actonel@and.dvt” rel=”nofollow”>.…
Buygeneric meds…
amalaki@fruit.buy” rel=”nofollow”>……
Buyno prescription…
levaquin 500mg…
Buy_no prescription…
antidepressant drugs for cats…
Buy_generic drugs…
financial aid for alzheimer’s…
Buy_without prescription…
heart worm medication…
Buy_no prescription…
best life diet…
Buy_no prescription…
blood pressure medications…
Buy_now it…
green nerf ds lite armour…
Buy_generic pills…
hepatitis b shot…
Buy_it now…
drug trafficking in latin america…
Buy_drugs without prescription…
bmi chart for kids…
Buy_generic drugs…
buy cheap clomid…
Buy_it now…
what is valtrex used for…
Buy_generic drugs…
permanent removal of adult acne scars…
Buy_generic meds…
symptoms of chocolate allergy…
Buy_it now…
enhancement drugs…
Buy_no prescription…
drug test company…
Buy_without prescription…
deadliest type of cancer…
Buy_generic pills…
social etiologies for patients with schizophrenia…
Buy_it now…
genie francis weight loss…
Buy_now…
zyrtec and drug tests…
Buy_generic meds…
epson salt and lemon juice diet…
Buy_generic pills…
l91 energizer e2 lithium aa cells…
Buy_generic drugs…
cordarone intravenous…
Buy_generic meds…
respiratory infection humidity dry antibiotics avelox…
Buy_generic pills…
find clomid cheap in us…
Buy_now…
how to buy glucose meter…
Buy_generic meds…
skin cancer foundation australia…
Buy_generic drugs…
depression medcine xr…
Buy_drugs without prescription…
what causes constant itching…
Buy_generic pills…
washington university marfan losartan study…
Buy_drugs without prescription…
clinical trial ediary data…
Buy_now it…
juliet’s on the spot acne treatment…
Buy_drugs without prescription…
school age adhd self absorbed…
Buy_now it…
neurontin and mood lability…
Buy_generic meds…
menses while on birth control pills…
Buy_generic drugs…
meal plan for hypertension…
Buy_generic drugs…
scabies in dogs help…
Buy_generic drugs…
herbals for pregnancy…
Buy_drugs without prescription…
can iodine help thyroid function…
Buy_generic drugs…
breven medication for add…
Buy_generic drugs…
information about treatment for leg edema…
Buy_generic drugs…
diet secrets for models…
Buy_now it…
dogs to detect cancer…
Buy_generic drugs…
best@medicare.plan.d.for.nexium.40.mg” rel=”nofollow”>..…
Buygeneric meds…
infant@prevacid.dose” rel=”nofollow”>.…
Buygeneric drugs…