Parse URL String to SQL

[primagic]

How would one parse an example of the following sql string to a table in my database:

IP_ADDRESS=10.10.10.10&LOAN_VAL=200000&CCJ=YES&IVA=YES&MISS_PYMT=YES&EST_VAL=1500000&PROP_DESC=HOUSE&PROP_PURP=PRIMARY&PROP_STYLE=TERRACE&SELF_CERT=YES&TIME_TO_APPLY=1_2WEEKS&BKCY=YES&OCC_STAT=EMPLOYED&CRED_GRADE=&LOAN_TYPE=ADJUSTABLE&PROP_COUNTY=Essex&PROP_POSTCODE=B92%209DP&BAL_ONE=500000&OTHER_DEBT=YES&UNSECURED_DEBT_AMOUNT=30000&SECURED_LOAN_AMOUNT=10000&TITLE=MR&FNAME=Joseph&LNAME=Grecko&DOBMM=11&DOBDD=20&DOBYYYY=1964&PRI_PHON=02075181524&PREF_CALLTIME=MORNING_PRIMARY&EMAIL=test@lptest.com&ADDRESS1=8704%20Hamsons&ADDRESS2=Apt%20#123&CITY=Solihull&COUNTY=Essex&POSTCODE=B92%209DP&SEC_PHON=07769779823&DOB=30/05/1976

 

[gmmastros]

Don't do this. Seriously. If you send data directly from a query string to the database, you expose yourself to SQL Injection.

Instead, you should be parsing the query string using your front end language, and validating the data before sending to the database.

You should make sure boolean values are limited to true/false. Numbers should be restricted to 0-9, the decimal and a negative sign. Phone numbers should be validated for length. Dates? Make sure they are valid also. The more validation you do on your data, the better your database will be, and the more valuable it will be. Trust me on this.

Once you parse the data to do your validation, it will be simpler to put the data in to the database using the parsed values.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

As George says, do this on the front end for many reasons. Also, parsing on the front end will be much easier than using SQL to do it. Many languages have a Split() method which would make parsing this a breeze.

 

[primagic]

Ok I have never done this before so I take all your comments on board. I am using access as a front end so I presume I would need to execute something in vba. Any ideas on where to start, any useful websites/articles to browse?

I also will need to do this in asp.net at some point to.

 

[jbenson001]

I can definately help you with .NET. Access I don't have much experiece with. I would assume that in .NET is is much easier with all the string functions available in the framework, whereas VBA will be much more limited.
When you do it .net and need any help, post in that forum and there are many people there to help.

 

[djj55]

There are also a VBA forum and an Access Modules forum.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!