SQL to Query Text in Access with an Apostrophe in It

SQL to Query text in access with an apostrophe in it

You escape ' by doubling it, so:

Select * from tblStudents where name like 'Daniel O''Neal' 

Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.

How to insert a value that contains an apostrophe (single quote)?

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

---

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.

Way to insert text having ' (apostrophe) into a SQL table

In SQL, the way to do this is to double the apostrophe:

'he doesn''t work for me'

However, if you are doing this programmatically, you should use an API that accepts parameters and escapes them for you automatically. Programmatically escaping and using string concatenation to assemble a query yourself is a sure way to end up with SQL injection vulnerabilities.

Is it possible to insert a value using an apostrophe using this command? Microsoft Access

Every time you need to pass values to execute an sql query you should ALWAYS use a parameterized query. As you have experienced, apostrophes mess with the syntax when you concatenate strings.

A parameterized query for your case should be

string value = "It's a nice day"
OleDbCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Table1 values(@value)";
cmd.Parameters.Add("@value", OleDbType.VarWChar).Value = value;
cmd.ExecuteNonQuery();

This will remove the problem with apostrophes, interpretation of the decimal point symbol, date format, but, most important even is not easy to exploit with Access, the Sql Injection ack.

I'm trying to query the customer name with an apostrophe but it looks like a special character?

that apostrophe is unicode character %u2019.
you can get that character using nchar() function

...
where contactname = N'O' + nchar(0x2019) + 'Brien, Dave'

unicode escaping tool

How to pass the string containing apostrophe to query in android?

The simplest and quickest way is to escape each single apostrophe with an extra apostrope, like this:

String fest = "Guru Rabindranath''s Birthday";

Or you can use rawQuery method's selectionArgs parameter (Sql Injection)

query = "Select desc from List where name = ?";
mDb.rawQuery(query, new String[] { fest });

Excel Vba - Using an apostrophe in an sql string

You can correct this either by using parameters (recommended) or by using Replace.

& Replace(txtDescription,"'","''") & 

Parameters

Dim cmd As New ADODB.command
cn.Open ServerConnect

cmd.ActiveConnection = cn

stSQL = "INSERT INTO Products (ProductName, " _
   & "ProductDescription, ProductUnit, SupplierID) " _
   & "Values (param1,param2,param3,param4)"

cmd.CommandText = stSQL
cmd.CommandType = adCmdText
With cmd
   .Parameters.Append .CreateParameter( _
         "param1", adInteger, adParamInput, , cboxItemNum)
   .Parameters.Append .CreateParameter( _
         "param2", adVarChar, adParamInput, 50, txtDescription )
   .Parameters.Append .CreateParameter( _
         "param3", adInteger, adParamInput, , txtUnit )
   .Parameters.Append .CreateParameter( _
         "param4", adInteger, adParamInput, , linkPID )
End with
cmd.Execute recs

Note that while I have named these parameters param1 to param4, that is for my convenience, all that matters is the order, which must match the order in which the parameters are to be used.

How do I search for names with apostrophe in SQL Server?

Double them to escape;

SELECT *
  FROM Header
 WHERE userID LIKE '%''%'

A better way to add apostrophes with SQL? - Access VBA

If you've been digging around here (and elsewhere) looking for sample code then you may have encountered the term SQL Injection. It is a mechanism whereby user input containing apostrophes (and other funny business) can have surprising and sometimes serious side-effects.

If you are operating within Access itself then you could save yourself some trouble by using a Recordset to update the table. Instead of running an INSERT statement you could use

Dim cdb As DAO.Database, rst As DAO.Recordset
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("tblContents", dbOpenDynaset)
rst.AddNew
rst!Contents = memoContents
rst.Update
rst.Close
Set rst = Nothing
Set cdb = Nothing

That way you don't need to worry about escaping characters or getting tripped up by SQL Injection.

---

Related Topics