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.
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.
How to properly add apostrophes into a mySQL INSERT command
PHP How to replace customers text area apostrophes with a escape character
$lastname = "O'Reilly";
$_lastname = mysqli_real_escape_string($lastname);
$query = "SELECT * FROM actors WHERE last_name = '$_lastname'";
SQL insert into database with apostrophe
Replace isn't the way to go here, you are already using a ADODB.Command
object so why not use a parameterised query.
Try this;
As you haven't provided information on your field types I can only speculate so instead I've added [datatype]
and [size]
placeholders for you to replace with ADO data type constants. A good resource for how data types in T-SQL map to ado is this article - Data Type Mapping
sql = ""
sql = sql & "INSERT INTO dbo.Jobs (" & vbCrLf
sql = sql & "JobID, CompanyName, DateReceived, DateOfDocument, ClientReference" & vbCrLf
sql = sql & ", Subject, TypeOfService,DueDate,AssignedAgent, ClientName, Plaintiff" & vbCrLf
sql = sql & ", Defendant1, Defendant2, Defendant3, CourtJurisdiction, Court" & vbCrLf
sql = sql & ", Subtype, CourtNumber, Amount, ServiceMethod, JobNotes, JobStatus" & vbCrLf
sql = sql & ", CreatedBy, CreatedDate" & vbCrLf
sql = sql & ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
With CreateJob
.ActiveConnection = "yourconnectionstring"
.CommandType = adCmdText
.CommandText = sql
'Add your parameters (all 24 of them in order)
'Assumed JobID is int which equates to adInteger ADO data type constant.
.Parameters.Append(.CreateParameter("@JobID", adInteger, adParamInput, 4))
.Parameters.Append(.CreateParameter("@CompanyName", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DateReceived", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DateOfDocument", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ClientReference", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Subject", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@TypeOfService", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DueDate", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@AssignedAgent", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ClientName", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Plaintiff", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant1", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant2", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant3", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CourtJurisdiction", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Court", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Subtype", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CourtNumber", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Amount", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ServiceMethod", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@JobNotes", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@JobStatus", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CreatedBy", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CreatedDate", [datatype], adParamInput, [size]))
'Specify your parameter values may need some conversion based on what you are passing.
.Parameters("@JobId").Value = Request.QueryString("jobid")
'Add the other 23 parameters as the above line.
'...
'Doing an INSERT no need to return recordset
Call .Execute(adExecuteNoRecords)
End With
Set CreateJob = Nothing
How to Insert Data in Database if it has Quotes or apostrophe
You need to replace the single quotes
with 2 single quotes
to escape
the single quote
string Address = txtAddress.Text;
Address = Address.Replace("'","''");
INSERT INTO with embedded apostrophe
you can replace that apostrophe with an double apostrophe, which is the standard SQL escape sequence for '
simply do that whith the following snippet:
+ "'" + _field[x].replaceAll("'", "''") + "', "
You could also use " instead of ' in your sql statement like the following:
+ "\"" + _field[x] + "\", "
Related Topics
Why Using a Udf in a SQL Query Leads to Cartesian Product
Parameterise Table Name in .Net/Sql
I Keep Getting the Error "Relation [Table] Does Not Exist"
Postgres - Where in (List) - Column Does Not Exist
Incomplete Information from Query on Pg_Views
Differencebetween SQL, Pl-SQL and T-Sql
How to Backup a Remote SQL Server Database to a Local Drive
How to Find a Table Having a Specific Column in Postgresql
Adding a New SQL Column with a Default Value
How to Cast a String to Integer and Have 0 in Case of Error in the Cast with Postgresql
Mysql: How to Select Groups Having Certain Values
Split Function in SQL Server 2008
Postgres SQL Insert Query Syntax Error from PHPpgadmin
How to Select Multiple Rows Filled with Constants
How to Write Update SQL with Table Alias in SQL Server 2008
Cannot Create an Instance of Ole Db Provider Microsoft.Jet.Oledb.4.0 for Linked Server Null