-1

I want to insert a query into a database which contain single quotes within the value. How can I handle this in PHP?

My query is: 

insert into query (date_time, userid, user_traits, query_sql, status, description, is_scheduled_row) 
values ('2016-01-06 02:39:01', '307', '0,3598,1937,13891,37746,22082,2596,2431,12850,3917,1234784,44712,14638,14418,12850,2631,25003,11428,27450,2592,23593,11441,2826,36330,32219,32351,20720,13997,2594,2467,15687', 'Select * from gl_base_schema.item where national_status_cd = 'A'', 'in queue', ' (Scheduled Query #413) Pull all items where National Status Code is 'A'', 1);

It shows error as 

ERROR 1064 (42000): 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 'A'', 'in queue', ' (Scheduled Query #413) Pull all items where National Status C' at line 1
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Gugan Abu
  • 546
  • 1
  • 4
  • 17

4 Answers4

1

Replace your single quote(') in value to BackSlash & Single Quote (\') or two single quotes ('')

Try this:

INSERT INTO QUERY (date_time, userid, user_traits, query_sql, STATUS, description, is_scheduled_row) 
VALUES ('2016-01-06 02:39:01', '307', '0,3598,1937,13891,37746,22082,2596,2431,12850,3917,1234784,44712,14638,14418,12850,2631,25003,11428,27450,2592,23593,11441,2826,36330,32219,32351,20720,13997,2594,2467,15687', 'Select * from gl_base_schema.item where national_status_cd = ''A''', 'in queue', ' (Scheduled Query #413) Pull all items where National Status Code is ''A''', 1);

OR

INSERT INTO QUERY (date_time, userid, user_traits, query_sql, STATUS, description, is_scheduled_row) 
VALUES ('2016-01-06 02:39:01', '307', '0,3598,1937,13891,37746,22082,2596,2431,12850,3917,1234784,44712,14638,14418,12850,2631,25003,11428,27450,2592,23593,11441,2826,36330,32219,32351,20720,13997,2594,2467,15687', 'Select * from gl_base_schema.item where national_status_cd = \'A\'', 'in queue', ' (Scheduled Query #413) Pull all items where National Status Code is \'A\'', 1);
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
0
$query = "Select * from gl_base_schema.item where national_status_cd = 'A'";
$sql = "insert into query (date_time, userid, user_traits, query_sql, status, description, is_scheduled_row) values ('2016-01-06 02:39:01', '307', '0,3598,1937,13891,37746,22082,2596,2431,12850,3917,1234784,44712,14638,14418,12850,2631,25003,11428,27450,2592,23593,11441,2826,36330,32219,32351,20720,13997,2594,2467,15687', '."'".$query."'".', 'in queue', ' (Scheduled Query #413) Pull all items where National Status Code is \'A\'', 1)";
Krishna Gupta
  • 695
  • 4
  • 15
0

You can use like that with double and single quote combination:

insert into query (date_time, userid, user_traits, query_sql, status, description, is_scheduled_row) 
VALUES ("2016-01-06 02:39:01","307","0,3598,1937,13891,37746,22082,2596,2431,12850,3917,1234784,44712,14638,14418,12850,2631,25003,11428,27450,2592,23593,11441,2826,36330,32219,32351,20720,13997,2594,2467,15687","Select * from gl_base_schema.item where national_status_cd = 'A'","in queue"," (Scheduled Query #413) Pull all items where National Status Code is 'A'", 1)

You can use 'A' this string into another string than you can use this as "'A' test"

devpro
  • 16,184
  • 3
  • 27
  • 38
-1

Where you have 'A'', make it 'A'''. The extra ' escapes the next ', so you need '' for one '. Hope that helps.

vard
  • 4,057
  • 2
  • 26
  • 46
PsyFer
  • 23
  • 3