Getting error while inserting data in table in MySQL

Question

My Query is:

 select a.lead_id,c.contactid,c.email,a.leadid,a.applicationid,a.appstatus,
    (case when c.state in ('AL','CA','GA','IL','NM','SC','UT','MO') then 'ADF' else 'FEB' end)originator,
     a.createdatetime,
     concat(CONCAT(UCASE(LEFT(c.FirstName, 1)),
     SUBSTRING(c.FirstName, 2)),
     ' ',
     CONCAT(UCASE(LEFT(c.LastName, 1)),
     SUBSTRING(c.LastName, 2))) as  'Name',
     address,CONCAT(UCASE(LEFT(c.city, 1)),
     SUBSTRING(c.city, 2)) as  'City',
     state,
     zipcode,
     concat(CONCAT(UCASE(LEFT(c.city, 1)),
     SUBSTRING(c.city, 2)) ,
     ' ',
     state,
     ' ',
     Zipcode)add2,SubscriberKey,b.TriggeredSendDefinitionObjectID,BounceCategory
     from reports.tbl_his_bounce_tracking b  join reports.tbl_his_triggeredsends k on k.ObjectID=b.TriggeredSendDefinitionObjectID
     join decision.contact c on c.email=b.SubscriberKey   join decision.application a using(lead_id)
     where k.Name like '%Email_Disclosure%'  and  EventType in (4,5) and date(b.CreatedDate)>=date(date_sub(convert_tz(now(),'UTC','US/pacific'),interval 1000 day)) limit 5  ;


when I m trying to insert its output in one table :


 insert into reports.NOD_Physical(lead_id,contactid,email,leadid,applicationid,appstatus,loan_originator,app_createdate,name,
    Address,City,state,zipcode,Address2,createdate,TriggeredSendDefinitionObjectID,BounceCategory) 
    values ('"+str(i[0])+"','"+str(i[1])+"','"+str(i[2])+"','"+str(i[3])+"','"+str(i[4])+"','"+str(i[5])+"','"+str(i[6])+"','"+str(i[7])+"','"+str(i[8])+"','"+str(i[9])+"','"+str(i[10])+"','"+str(i[11])+"','"+str(i[12])+"','"+str(i[13])+"','"+str(i[14])+"','"+str(i[15])+"','"+str(i[16])"');

It is getting inserted successfully but when it encounters a name Janice O'Neal.Its throwing the below error due to ' in Janice O'Neal.

Error : (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Neal','121 Twelve Oaks Dr','Lagrange','GA','30241','Lagrange GA 30241','janiceon' at line 1")

Possible duplicate of [How to escape apostrophe (') in MySql?](https://stackoverflow.com/questions/9596652/how-to-escape-apostrophe-in-mysql) — vahdet, Jun 26 '18 at 17:42
While vahdet's suggestion should address the issue, you should really look into using parameterized queries. — Uueerdo, Jun 26 '18 at 17:45
I love ireland (more or less) names as test to every SQL. My native polish has not simmilar feature :) I had kill many, many "perfect" queries — Jacek Cz, Jun 26 '18 at 18:30

score 0 · Answer 1 · answered Jun 26 '18 at 18:08

0

The apostrophe ' in Janice O'Neil should be used as '' when inserting values in MySQL.

Try changing to: Janice O''Neil and see if that fixes your problem. Source

answered Jun 26 '18 at 18:08

Zack

2,296
20
28

score 0 · Answer 2 · answered Jun 27 '18 at 14:27

I got the answer: My Insert query is enclosed by "", when ever i encounter "'" (an apostrophe) " will get trminated at that point itself and remaining part will not get cover under these quotes. That is reason it was throwing an error.

Solution: dbinsert='''insert into reports.NOD_Physical(lead_id,contactid,email,leadid,applicationid,appstatus,loan_originator,app_createdate,name,Address,City,state,zipcode,Address2,createdate,TriggeredSendDefinitionObjectID,BounceCategory) values ("'''+str(i[0])+'''","'''+str(i[1])+'''","'''+str(i[2])+'''","'''+str(i[3])+'''","'''+str(i[4])+'''","'''+str(i[5])+'''","'''+str(i[6])+'''","'''+str(i[7])+'''","'''+str(i[8])+'''","'''+str(i[9])+'''","'''+str(i[10])+'''","'''+str(i[11])+'''","'''+str(i[12])+'''","'''+str(i[13])+'''","'''+str(i[14])+'''","'''+str(i[15])+'''","'''+str(i[16])+'''");'''

Getting error while inserting data in table in MySQL

2 Answers2