Ignore apostrophe in sql query from text box

Question

I'm thoroughly enjoying my stay here at stack overflow, I have found more useful information than I can count.

However, through all of my searches, I have yet to find an answer to my issue.

I have a Winforms app that inputs data into an SQL database. I have an issue with the text box in this form posting to the database.

If a user inputs an apostrophe or a quotation mark the query in Visual studio stops at where that apostrophe is in the text box even if there is more data after that and does not enter the data into the database.

I know that putting a second apostrophe will cancel out the one that was input, however, I can't seem to read the data in the text box before the query executes to cancel them out. I have put an example of what works in this text box and what breaks the query in this question for further clarification.

I apologize, everyone, it seems I forgot to put my code into this post.

        Dim InsertQuery As String = "INSERT INTO SelfInstallNotes (Troubleshooting, DateAndTime, [CL to OD], [Swapped Dscntd Clocks], [Upgrading to SaaS], [Update Version], [Created RPF], [Created RMA], [Clock Serial], [Case Number], [User], [Grabbed], [Account Manager], [Transferred to AM]) VALUES('" & Hidden.TextBox6.Text.ToString & "'" & "," & "'" & DateTimePicker1.Value & "'" & "," & "'" & CheckBox1.CheckState & "'" & "," & "'" & CheckBox2.CheckState & "'" & "," & "'" & CheckBox3.CheckState & "'" & "," & "'" & CheckBox4.CheckState & "'" & "," & "'" & CheckBox5.CheckState & "'" & "," & "'" & CheckBox6.CheckState & "'" & "," & "'" & TextBox2.Text & "'" & "," & "'" & TextBox1.Text & "'" & "," & "'" & TextBox6.Text & "'" & "," & "'" & Hidden.TextBox3.Text & "'" & "," & "'" & Hidden.TextBox4.Text & "'" & "," & "'" & Hidden.TextBox5.Text & "'" & ")"

But after reading all of you answers this code is most certainly WRONG and can cause issues later on down the road. I will try the answer posted below and update the thread accordingly.

This query will not post to DB due to the apostrophe in can't

Screen shot with apostrophe

while this input in the textbox will post without any issues, as there is no apostrophe or quotation marks.

Screenshot without apostrophe

you need to read up on SQL Parameters, and SQL Injection. Performing straight inserts into the DB from user entry fields is a BAD IDEA. — Stephen Wrighton, Sep 24 '18 at 19:22
How do you construct your query? Never do it just by concatenating parameters as strings - that will open you up to SQL injection attacks. — 500 - Internal Server Error, Sep 24 '18 at 19:22

Joel Coehoorn · Answer 1 · 2018-09-24T20:04:27.647

The problem is worse than you know. This is also a huge security issue. Try putting the following text in your input:

'; DROP TABLE [MyCallTable];--

Or don't, if you value your data.

Fortunately, the solution is the same for both the security issue and for normal, everyday apostrophes; you quarantine all user input from the rest of the SQL command by using query parameters.

Here's an example:

var SQL = "SELECT * FROM Users WHERE LastName = @LastName";
using (var cn = new SqlConnection("connection string here"))
using (var cmd = new SqlCommand(SQL, cn))
{
    //TextBox1 can have text with ' characters, and it won't matter.
    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 25).Value = TextBox1.Text;

    cn.Open();
    DataGrid1.DataSource = cmd.ExecuteReader();
}

There are other forms of this, too, depending on your environment, but the important thing is the text input is never at any time substituted directly in the SQL command — not even on the database server. This prevents any possibility of bad input injecting into the SQL. It also makes it easier to get things like date formats right, as now the ADO.Net provider will handle conversions for you.

The main thing to understand is if you ever find yourself doing anything like this:

sql = "SELECT * FROM [Table] WHERE Field='" + TextBox.Text + "'";

or even this:

sql = "SELECT * FROM [Table] WHERE Field='" + TextBox.Text.Replace("'", "''") + "'";

you're doing something very wrong.

This is one of those things that's important enough it's worth going back through an old code base to fix every instance where you've done it the wrong way before the next release, and it's not often I'll say that.

score -8 · Answer 2 · answered Sep 24 '18 at 20:17

-8

var stringvariable = tempString.Replace("'", "''");// this escapes the single quote

answered Sep 24 '18 at 20:17

China Syndrome

953
12
24

2

This is the wrong way to do it and just plain bad advice. – Joel Coehoorn Sep 24 '18 at 20:17

Ignore apostrophe in sql query from text box

2 Answers2