Not sure how many SQL gurus we have here...

[PackerFanatic]

But this question is bugging me and I can't figure it out.

I have this SQL statement I wrote for work and it seems to have an error when one certain thing is entered. Here is the statement:

"SELECT TRTEXT_ID, TRTEXT_TEXT_NUM, TRTEXT_PUBLISHER_TXT, TRTEXT_AUTHOR_TXT, TRTEXT_TITLE_TXT, TRTEXT_EDITION_NUM, TRTEXT_ISBN_TXT, TRTEXT_LISTPRICE_AMT, TRTEXT_ADOPTION_TRM, TRTEXT_DELETION_TRM, TRTEXT_STATUS_CD, TRTEXT_SHELFLOC_TXT, TRTEXT_SYSDATE_TMS, TRTEXT_USER_NM, TRTEXT_COMMENTS_TXT
FROM TRTEXT
WHERE (TRTEXT_STATUS_CD = 'ACTIVE') AND (TRTEXT_"+searchOption+"_TXT LIKE '"+tbSearch.Text.ToUpper()+"%')ORDER BY TRTEXT_"+searchOption+"_TXT"

The bolded area is the place where we are having an issue. As you can see, there are single quotes around the search text and % (which is needed for the like statement) The problem is when the user enters a single quote in that text field (like they are searching for O'Rourke for instance, or something to that effect) It bombs the statement, since there is then a quote not ended. I have tried using parameters but I am not sure how to do it in the middle of a statement like that while still keeping the single quotes and all that. This is an Oracle statement (as opposed to an OleDB one), not sure if that makes a difference. Thanks in advance guys.

[Coffee Warlord]

If this is called from the web, backslash the search criteria before you pass the statement to SQL.

[Glengoyne]

I think you'll have to "preprocess" the string you are passing to the dynamic query. You will need to escape the single quote in your search text. In oracle you would use another single quote to escape the single quote. LIke O''Rourke. Those are two single quotes.

YMMV

[Celeval]

Yeah, best bet is to run some sort of encode() on tbSearch.Text first to either strip out or escape SQL-breaking queries. You'll also want to handle % signs in there.

[PackerFanatic]

Ah, so basically I would have to search through the text for any characters that would screw up my statement and escape them. Got it. I will work on this now! Thanks guys!

[Taco]

Just be careful when you assemble SQL statements like this based on user input. For example, what would happen if the user entered: ;DELETE TRTEXT;

Security holes like this could allow users to run arbitrary SQL statements. It may not be important for your app, but it's something to keep in mind.

[MikeVic]

Originally Posted by Taco

Just be careful when you assemble SQL statements like this based on user input. For example, what would happen if the user entered: ;DELETE TRTEXT;

Security holes like this could allow users to run arbitrary SQL statements. It may not be important for your app, but it's something to keep in mind.

I believe this is called SQL Injection? And I want to voice my agreement with this too. Watch out how you apply user's input to an SQL statement.