Standard treatment for a text to be used in select

Question

0.00/5 (No votes)

See more:

Suppose that a user enters a text with words to search in a Select Sql of Sql Server. Is there a standard way to treat c # text to make it valid? It happens that from time to time my program crashes because while testing I have introduced a new invalid character. The last one was with the word "house's".

I use a function that composes Sql code, so the text that the user enters is assigned to the corresponding part of the Select ContainsTable. The problem that I indicate is that, if the user enters for example a single quote or a parenthesis, then the command fails. So I ask if there is a standard way to treat that text.

I show a possible treatment function:

C#

public static string Fcn_Text_for_Sql(string Text)
{   string Text_Return = Text;

            try
            {   
                char[] TextArr = Text_Return.ToCharArray();
                System.Text.StringBuilder MyStringBuilder = new System.Text.StringBuilder(Text_Return.Length);

                // 1.- REMOVE CONTROL CODES.
                foreach (char ch in TextArr)
                {   // Válid Code. StringBuilder. Append.
                    if (!System.Char.IsControl(ch)) MyStringBuilder.Append(ch);
                }

                // Convert. MyStringBuilder to String.
                Text_Return = MyStringBuilder.ToString();

                // 2.- REPLACE CODES.
                Text_Return = Texto_Retorno.Replace("'", "''");
                Text_Return = Texto_Retorno.Replace("\"", "\""); // NOT VALID.

                // 3.- REMOVE CODES.
                Text_Return = Text_Return.Replace("(", null);
                Text_Return = Text_Return.Replace(")", null);

                // 4.- ETC...
            }
            catch(System.Exception ErrorExcp){  }
            return Text_Return;
        }

What I have tried:

in my code and in internet.in my code and in internet.

Posted 10-Oct-21 3:18am

Member 14890678

Updated 10-Oct-21 5:34am

v3

Add a Solution

Comments

Richard MacCutchan 10-Oct-21 9:28am

Where does the error occur and why? If your search method has restrictions on what characters are allowed then you need to test for them. If not then the search term "house's" could be valid.

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)

RickZeeland · Answer 1 · 2021-10-10T03:47:00

Solution 1

You should use parameterized queries, see answer here:
how to handle special character within the query..?[^]

Posted 10-Oct-21 3:47am

RickZeeland

OriginalGriff · Answer 2 · 2021-10-10T03:57:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myColumn1, myColumn2) VALUES (@C1, @C2)", con))
        {
        cmd.Parameters.AddWithValue("@C1", myValueForColumn1);
        cmd.Parameters.AddWithValue("@C2", myValueForColumn2);
        cmd.ExecuteNonQuery();
        }
    }

And then you don't have to change the data at all to make it all work ...