How do I Include apostrophe to Textbox without Error?

Question

I have a gridview that displays results from a textbox or dropdown filter. It works fine except when a user uses an apostrophe in the search. For example "Tommy's Company" will result in an error message that reads "The expression contains an invalid string constant: '." I've been at it for days and I can't figure it out. Is there anyway I can include the apostrophe into the textbox and still get the results without the error message?

Here's the HTML portion:

        <asp:SqlDataSource ID="NewBiddersDBsource" runat="server" ConnectionString="<%$ ConnectionStrings:BiddersDBconnection %>" 
            SelectCommand="SELECT_DefaultContractors" SelectCommandType="StoredProcedure" FilterExpression="{0} LIKE '%{1}%'"> 
            <SelectParameters>
                <asp:ControlParameter ControlID="SearchBox" DefaultValue="%" Name="Name" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="SearchBox" DefaultValue="%" Name="City" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="DDLStatus" DefaultValue="%" Name="Status" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="WorkList" DefaultValue="%" Name="WorkID" PropertyName="Text" Type="String" />
            </SelectParameters>
            <FilterParameters>
                <asp:ControlParameter ControlID="searchList" Name="SearchCategory" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="SearchBox" Name="SearchField" PropertyName="Text" />
                <asp:ControlParameter ControlID="WorkList" Name="WorkID" PropertyName="SelectedValue" />
            </FilterParameters>
        </asp:SqlDataSource>

" target="_blank">Select

This is the C# portion:

string connectionString = ConfigurationManager.ConnectionStrings["BiddersDBConnection"].ConnectionString; SqlConnection conn = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;

        cmd.CommandType = CommandType.StoredProcedure;

        string nameSearch = SearchBox.Text.Replace("'","''");
        cmd.CommandText = ("SELECT_AllBidders");
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 200).Value = nameSearch;

        conn.Open();
        cmd.ExecuteNonQuery();

This is from the SQL portion:

CREATE PROCEDURE [dbo].[SELECT_DefaultContractors]
@Name varchar, @City varchar, @Status varchar, @WorkID varchar(50)
AS
BEGIN

SET NOCOUNT ON;


SELECT DISTINCT
    Bidders.Id, Bidders.Name, Bidders.Address, Bidders.City, Bidders.State,
    Bidders.Zip, Bidders.Phone, Bidders.Fax, Bidders.Email, Bidders.Status,
    Bidders.Denied, MWBE_Types.Code AS MWBE
FROM 
    PW_Contractors.dbo.Bidders LEFT JOIN PW_Contractors.dbo.Preqs ON 
    PW_Contractors.dbo.Bidders.Id = PW_Contractors.dbo.Preqs.BidderID
    LEFT JOIN PW_Contractors.dbo.MWBE_Types ON PW_Contractors.dbo.Bidders.MWBE =
    PW_Contractors.dbo.MWBE_Types.MWBEID
WHERE 
    Name LIKE '%' + @Name + '%' OR
    City LIKE '%' + @City + '%' AND Status LIKE '%' + @Status + '%' AND
    WorkID LIKE @WorkID  OR WorkID IS NULL ORDER BY Name ASC;
END

In SQL to escape apostrophes you have to escape them by using 2 apostrophes. `'` needs to be `''` — Pedro Estrada, Sep 22 '16 at 21:06
Pedro please elaborate. How would you rewrite Where Name LIKE '%' + @NAME + '%' portion? I have thought about modifying it in the SQL portion but wasn't sure how to go about it. — user3736492, Sep 22 '16 at 21:19

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

I believe you must escape the character for it to be valid try something like this:

s = s.Replace("'", @"\'");

More specifically:

single quotes escape during string insertion into a database

Would probably get you going in the right direction as well.

edited May 23 '17 at 12:06

Community

1
1

answered Sep 22 '16 at 21:05

confusedandamused

746
2
8
28

I read that you're supposed to use parameters to avoid SQL injection attacks, but so far that doesn't seem to be working. Unless I've been approaching this the wrong way this whole time. – user3736492 Sep 22 '16 at 21:30
Are you querying the database on the search or are you inserting? Just to be clear. – confusedandamused Sep 22 '16 at 21:36
I'm querying the database on search via textbox – user3736492 Sep 22 '16 at 21:37
@user3736492 You have tried replacing string nameSearch = SearchBox.Text.Replace("'","''"); with string nameSearch = SearchBox.Text.Replace("'", @"\'"); ? – confusedandamused Sep 23 '16 at 01:35
It also seems that using Paramaterized queries can solve this issue as well as the possibility for SQL injection issues. [This](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx) here is the documentation for it. [Here](http://stackoverflow.com/questions/6547986/how-to-prevent-a-sql-injection-escaping-strings) is some good discussion on the topic at hand. Hope this helps! :) – confusedandamused Sep 23 '16 at 01:48
I'm doing this query via a Paramter. Please reference the above codes. I tested this without the stored procedure and other filters; using only a SQL statement Select * From Table. Then I used a Control Parameter via SearchBox and it was able to accept the apostrophe just fine. So I don't know why this would work as a plain SQL statement but not through the stored procedure. – user3736492 Sep 23 '16 at 19:49

score 0 · Answer 2 · answered Sep 23 '16 at 15:34

I believe that the issue you are having is with the FilterExpression property. I don't think it allows single quotes.

Below is a sample code I tested.

<asp:GridView ID="gvtest" runat="server" AutoGenerateColumns="true" DataSourceID="NewBiddersDBsource"></asp:GridView>
<asp:SqlDataSource 
    ID="NewBiddersDBsource" 
    runat="server" 
    ConnectionString="<%$ ConnectionStrings:MRP %>" 
    SelectCommand="SELECT Name FROM dbo.tmptest WHERE Name LIKE '%' + @Name + '%'">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="Name" PropertyName="Text" Type="String" DefaultValue="%"  />
        </SelectParameters>
        <FilterParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="Name" PropertyName="SelectedValue" />
        </FilterParameters>
</asp:SqlDataSource>
<asp:DropDownList
    id="DropDownList1"
    runat="server"
    AutoPostBack="True">
    <asp:ListItem Selected="True">test</asp:ListItem>
    <asp:ListItem>test1'</asp:ListItem>
    <asp:ListItem>aserewa</asp:ListItem>
</asp:DropDownList>

score 0 · Answer 3 · answered Jul 17 '20 at 09:01

The right syntax for replacement in c # and sql server 2019, it is so ...

from:

string nameSearch = SearchBox.Text.Replace("'","''");

a:

string nameSearch = SearchBox.Text.Replace("'",@"''");

But the right method to use is not the replacement of the apostrophe, but to use this method (see code below) which is the one to use if you have to enter data and select data.

Example: To insert data in the table

 string Query = "insert into Pizza ( NomeC ) values (@NomeC)";  
 SqlCommand createCommand = new SqlCommand(Query, loginConn);
 createCommand.Parameters.AddWithValue("@NomeC", Nome_txt.Text);
 createCommand.ExecuteNonQuery();
 ...

Example to read the data in the table

 string Query = "select * from Pizza where NomeC= @NomeC";
 SqlCommand createCommand = new SqlCommand(Query, loginConn);
 createCommand.Parameters.AddWithValue("@NomeC", Nome_txt.Text);
 SqlDataReader dr = createCommand.ExecuteReader();
 ...

This is the right way to insert the apostrophe in c # and sql server

How do I Include apostrophe to Textbox without Error?

3 Answers3