How to prevent SQL injection for query which has between clause?

Question

0.00/5 (No votes)

See more:

The below i is my query

SQL

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

I have tried with below

C#

string query = SELECT * FROM Products
WHERE Price BETWEEN @fromNum AND @toNum;";

cmd.Parameters.Add("@fromNum" fromNumber);
cmd.Parameters.Add("@toNum" toNumber);

getting missing expression.

What I have tried:

I have tried with below

C#

string query ="SELECT * FROM Products
WHERE Price BETWEEN @fromNum AND @toNum;";

cmd.Parameters.Add("@fromNum" fromNumber);
cmd.Parameters.Add("@toNum" toNumber);

getting missing expression.

Posted 19-Jun-20 4:45am

DGKumar

Updated 19-Jun-20 6:41am

MadMyche

v2

Add a Solution

Comments

MadMyche 19-Jun-20 10:54am

Can you post the actual Exception you received?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-06-19T05:24:00

Solution 1

I don't have Oracle - for which I'm pretty glad, if I'm honest - but with SQL Server, I'd suspect the problem is that you aren't using the query string - you don;t show in your code that the cmd object is connected to the query object, just that the parameter values are set.

No query in Oracle would seem to generate a "missing expression" error: ORA-00936: missing expression | TekStream[^] so I'd start there.

Posted 19-Jun-20 5:24am

OriginalGriff

Comments

DGKumar 21-Jun-20 13:51pm

Please correct me something which is wrong in the below query
SELECT *
FROM tab1
WHERE timestamps BETWEEN TO_DATE ('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');
How to add parameterised query for the above command
string query ="SELECT * FROM Products
WHERE Price BETWEEN :fromDate AND :toDate;";

cmd.Parameters.Add(":fromDate" fromDate);
cmd.Parameters.Add(":toDate" toDate);

F-ES Sitecore · Answer 2 · 2020-06-19T06:42:00

Solution 2

Use AddWithValue and make sure to stipulate an int type, and also ensure fromNumber and toNumber are ints. If not use int.TryParse to convert them to ints. Those things will prevent SQL injection.

Posted 19-Jun-20 6:42am

F-ES Sitecore

Comments

DGKumar 21-Jun-20 13:51pm

Please correct me something which is wrong in the below query
SELECT *
FROM tab1
WHERE timestamps BETWEEN TO_DATE ('2015-05-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2015-04-06T15:39:00', 'YYYY-MM-DD"T"HH24:MI:SS');
How to add parameterised query for the above command
string query ="SELECT * FROM Products
WHERE Price BETWEEN :fromDate AND :toDate;";

cmd.Parameters.Add(":fromDate" fromDate);
cmd.Parameters.Add(":toDate" toDate);