sql function to return table of names and values given a querystring

Question

Anyone have a t-sql function that takes a querystring from a url and returns a table of name/value pairs?

eg I have a value like this stored in my database:

foo=bar&baz=qux&x=y

and I want to produce a 2-column (key and val) table (with 3 rows in this example), like this:

name  | value
-------------
foo   | bar
baz   | qux
x     | y

UPDATE: there's a reason I need this in a t-sql function; I can't do it in application code. Perhaps I could use CLR code in the function, but I'd prefer not to.

UPDATE: by 'querystring' I mean the part of the url after the '?'. I don't mean that part of a query will be in the url; the querystring is just used as data.

Answer 1

create function dbo.fn_splitQuerystring(@querystring nvarchar(4000))
returns table 
as
/*
 * Splits a querystring-formatted string into a table of name-value pairs
 * Example Usage:
        select * from dbo.fn_splitQueryString('foo=bar&baz=qux&x=y&y&abc=')
 */
return ( 
    select  'name' = SUBSTRING(s,1,case when charindex('=',s)=0 then LEN(s) else charindex('=',s)-1 end) 
        ,   'value' = case when charindex('=',s)=0 then '' else SUBSTRING(s,charindex('=',s)+1,4000) end    
    from dbo.fn_split('&',@querystring)
)
go

Which utilises this general-purpose split function:

create function dbo.fn_split(@sep nchar(1), @s nvarchar(4000))
returns table
/*
 * From https://stackoverflow.com/questions/314824/
 * Splits a string into a table of values, with single-char delimiter.
 * Example Usage:
        select * from dbo.fn_split(',', '1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,')
 */
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
  )
go

Ultimately letting you do something like this:

select name, value
from dbo.fn_splitQuerystring('foo=bar&baz=something&x=y&y&abc=&=whatever')

Answer 2

I'm sure TSQL could be coerced to jump through this hoop for you, but why not parse the querystring in your application code where it most probably belongs?

Then you can look at this answer for what others have done to parse querystrings into name/value pairs.

Or this answer.

Or this.

Or this.

Answer 3

Please don't encode your query strings directly in URLs, for security reasons: anyone can easily substitute any old query to gain access to information they shouldn't have -- or worse, "DROP DATABASE;". Checking for suspicious "keywords" or things like quote characters is not a solution -- creative hackers will work around these measures, and you'll annoy everyone whose last name is "O'Reilly."

Exceptions: in-house-only servers or public https URLS. But even then, there's no reason why you can't build the SQL query on the client side and submit it from there.