sql in razor script

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Jon 7 posts 88 karma points

Mar 08, 2021 @ 23:37
0

SQL in Razor Script
I've got a user table for comments to my web site. I want to check a new comment to insure that I don't have duplicate names with the same email. I've written a method that calls the following:
```
            string theSql = "select userEmail from userComments " +
                        "where userName = \'" + name + "\' and userEmail != \'" + email + "\'";
        IList<string> result = theDB.Fetch<string>(theSql);
```
If the email is '[email protected]' it throws an error that parameter @foo does not have a value. When I run the same code in SQL Server Management studio it works fine. I'm using version 7.28
Copy Link
Brendan Rice 538 posts 1102 karma points

Mar 09, 2021 @ 00:26
101
Hey Jon,

have a look at the docs below:

https://our.umbraco.com/documentation/Tutorials/Creating-Tables-for-Umbraco-with-PetaPoco/

It seems like you're looking for something like this:
```
List<string> result = db.Fetch<string>("select userEmail from userComments where userName = @0 and userEmail != @1", name, email);
```
I hope this helps...
Copy Link
Jon 7 posts 88 karma points

Mar 09, 2021 @ 15:24

1

Thanks Brendan - that fixed it. Thanks also Dan and Nik for your help.

Copy Link
Daniel Pazos 12 posts 123 karma points

Mar 09, 2021 @ 09:52
0
If Brendan's answer doesn't work for you, you can try formating ths string with $.

For example:
```
List<string> result = db.Fetch<string>($"select userEmail from userComments where userName = '{name}' and userEmail != '{email}'");
```
Don't forget to wrap the strings in single quotes.
Copy Link
Nik 1614 posts 7260 karma points MVP 7x c-trib

Mar 09, 2021 @ 09:57

1

Hey Jon,

Please, please, please, read the comments from Brendan and Daniel carefully.

You're current approach is potentially open to SQL injection attack depending where the values for "name" and "email" are coming from. Even wrapping in ' won't stop someone putting ' in inputs and escaping your SQL to cause harm.

Brendans approach is the better one as as it is using parameters to safely query the data.

Thanks

Nik

Copy Link
Brendan Rice 538 posts 1102 karma points

Mar 09, 2021 @ 10:58

0

Info on SQL injection

Copy Link
is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?