Can I concatenate a query?

Question

0.00/5 (No votes)

See more:

I've always heard that it's not a good practice to concatenate strings into the values of a Query. But simplistically, is there any problem in concatenating the query like this?

Query = "SELECT "
Query += "colA, "
Query += "colB, "
Query += "colC "
Query += " FROM SomeTable"
Query += " Where colA = @Value;"

Note that I haven't concatenated the value, I'm passing @Value, and I'm going to treat it as a parameter in my application.

What I have tried:

I've been doing just that, but I really don't know if I'm wrong.

Posted 29-Jul-21 10:20am

Gdno

Updated 29-Jul-21 12:35pm

v2

Add a Solution

3 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 · Accepted Answer · 2021-07-29T11:21:00

Solution 1

That's not a problem - in fact with big queries it's a good idea because otherwise they become unreadable.

As long as you don't concatenate the content of any variables (i.e. values) you are fine.

Posted 29-Jul-21 11:21am

OriginalGriff

Comments

Gdno 29-Jul-21 17:50pm

Thank you friend.

OriginalGriff 29-Jul-21 17:56pm

You're welcome!

Dave Kreskowiak · Accepted Answer · 2021-07-29T11:49:00

Solution 2

Just don't go nuts with it otherwise your going to drive yourself insane looking for some obscure typo in your concatenation code. You already have a bit of a typo just in the code you posted!

Simplify it a bit!

C#

query = "SELECT colA, colB, colC"
query += " FROM SomeTable"
query += " WHERE colA = @Value"

Posted 29-Jul-21 11:49am

Dave Kreskowiak

Comments

Gdno 29-Jul-21 17:56pm

Thanks, but the code was just a pretty crude way of demonstrating my doubt.

Dave Kreskowiak 29-Jul-21 18:07pm

Yeah, and even in your example, you have an extra comma that you didn't notice or fix. You just provided an example of what people new to building SQL queries miss or don't understand.

Every time you insert a new concatenation, you introduce more opportunities to screw something up with just the tiniest mistake, like missing a space character or adding a comma where it doesn't belong.

What I posted is just a "heads up" to look for the tiny mistakes because they WILL get in there and drive you nuts trying to find the problem. We've seen it over and over and over in the forums.

Gdno 29-Jul-21 18:20pm

I get it, thanks for sharing.

Patrice T · Accepted Answer · 2021-07-29T12:35:00

To add to other solutions :

Quote:
Can I concatenate a query?

The way you did it, it is safe, the problem is when you concatenate user input values in query.
Something common is:

C#

Query = "SELECT "
Query += "colA, "
Query += "colB, "
Query += "colC, "
Query += " FROM SomeTable"
if (condition1)
    Query += " Where colA = @Value;"
elseif (condition2)
    Query += " Where colB = @Value;"
else
    Query += " Where colC = @Value;"

This make 3 different queries depending on conditions, it can safely get much more complicated.
What is dangerous is:

C#

UserInput= "My input;drop  SomeTable"
Query = "SELECT "
Query += "colA, "
Query += "colB, "
Query += "colC, "
Query += " FROM SomeTable"
Query += " Where colA = "
Query += UserInput
Query += ";"

Thanks to malicious input, you end up with this query :

SQL

SELECT colA, colB, colC, FROM SomeTable Where colA = My input;
drop  SomeTable;

User input is promoted to code. That is "SQL Injection"