How to add parameters to WebMatrix Database.Query?

Question

Using Database.Query has has made a huge improvement on readability in my code:

String Select = 'sp_getAllUsers';
WebMatrix.Data.Database DB = WebMatrix.Data.Database.Open(MyConString);
var data = DB.Query(Select);

I'd like to switch to a non-stored procedure query. The MSDN says there's an optional parameter to the Query Method, Object[], that can be passed as SQL parameters, however they don't have any further information about it.

So I have two questions:

How can I create a Object[]?
Will adding parameters in this way prevent hacking threats, such as SQL Injection?

Here's an abridged version of what I have tried:

Select = "Select * From Users Where first_name = "Foo" AND last_name = "Bar"

// Like Javascript
Object[] params = {"first_name" : "Foo"}, {"last_name" : "Bar"};

// More Like What I think it will be
Object[] Params = (String Name = "first_name", String First_Name = "Foo");

var data = DB.Query(Select, params);

All the sources I've looked at only seem to reference the old way. This is close, but he doesn't use the parameter parameter of the Query method.

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented May 20, 2015 at 21:00

5 revs · Accepted Answer · 2015-05-21 02:05:38Z

5

Try using this syntax:

string selectCommand = "sp_getAllUsers(@0, @1)";
// selectCommand = "Select * From Users Where first_name = @0 AND last_name = @1";
...
var data = DB.Query(selectCommand, "Foo", "Bar");

More info, see: http://www.aspnet101.com/2010/07/webmatrix-tutorial-working-with-data/

Also, using a Parameter will always prevent SQL Injection as it always double quote a single quote.

edited May 21, 2015 at 2:05

community wiki

5 revs
Carl Prothman

Sign up to request clarification or add additional context in comments.

3 Comments

Travis Heeter Over a year ago

This worked, however, my other question wasn't answered, Will this method of adding parameters prevent SQL injection? Or should I do this: Select * From Users Where first_name = N'@0' AND last_name = N'@1'?

Carl Prothman Over a year ago

Using Parameters will always prevent SQL Injection as they always double quote a single quote.

Carl Prothman Over a year ago

if this solved your issue, please make it as the solution. Thanks! :)

Collectives™ on Stack Overflow

How to add parameters to WebMatrix Database.Query?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related