AJAX won't insert data with single quotes into SQL

Question

I have the following variable grabbing data from HTML:

    $workcarriedout = trim($_POST["workcarriedout"]);

And the following statement inserting this entry into SQL database (not a mySQL database):

$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (work_carried_out)
                    values ('".$workcarriedout."')");

This works perfectly fine until the data entered into the variable contains single quotes.

Could somebody please advise how I can get the data to import with the single quotes?

Edit: If this makes a difference here is the code:

<?php
require_once('../settings.php');
// Get the form fields and remove whitespace
var_dump($_POST);

$datetime = trim($_POST["datetime"]);
$servername = trim($_POST["servername"]);
$carriedoutby = trim($_POST["carriedoutby"]);
$workverifiedby = trim($_POST["workverifiedby"]);
$authorisedby = trim($_POST["authorisedby"]);
$workcarriedout = trim($_POST["workcarriedout"]);
$howverified = trim($_POST["howverified"]);
$reason = trim($_POST["reason"]);
$impact = trim($_POST["impact"]);
$rollback = trim($_POST["rollback"]);


try {
    $db = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}


$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (date_time, server_name, carried_out_by, verified_by, authorised_by, work_carried_out, work_verified, change_reason, perceived_impact, rollback_process)
                    values ('$datetime','$servername','$carriedoutby','$workverifiedby','$authorisedby','$workcarriedout','$howverified','$reason','$impact','$rollback')");

$stmt->execute();



socket_close($socket);

?>

You would need to use prepare statement in the proper manner. Use bind parameter. — ajreal, Jun 22 '17 at 11:16
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — iainn, Jun 22 '17 at 11:34

score 0 · Answer 1 · answered Jun 22 '17 at 11:52

0

Try this code to escape single code then insert to db:

$workcarriedout = mysqli_real_escape_string($db, $workcarriedout);
$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (work_carried_out) values ('".$workcarriedout."')");

answered Jun 22 '17 at 11:52

MahdiY

1,269
21
32

score 0 · Answer 2 · answered Jun 22 '17 at 12:00

0

The quotes you have in the string are breaking the SQL insert string. To avoid this try:

$escaped_string = $db->escape_string($workcarriedout); // does the job with the quotes

$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (work_carried_out)
                values ('".$escaped_string."')");

answered Jun 22 '17 at 12:00

Machado

8,965
6
43
46

Hi Machado - thanks for the comment, with this the request won't go through – Harry Jun 22 '17 at 12:23

Zeljka · Answer 3 · 2017-06-22T13:47:40.610

0

$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (`work_carried_out`)
                values ('\'$workcarriedout\'')");

I test it and it works now

somethings wrong but I cant see what, only difference between your and mine query is

(`date_time`, `server_name` etc..)

this is my query, and it works :/

$stmt = $db->prepare("INSERT INTO zeljka (`name`,`lastName`) VALUES ('\'$st\'', '\'$sta\''); and it works :////

screenshot inserted data

edited Jun 22 '17 at 13:47

answered Jun 22 '17 at 12:04

Zeljka

376
1
10

Hi Zeljka - thanks for the comment, unfortunately this didn't work. Data went into the table without ' but not with – Harry Jun 22 '17 at 12:13
sorry my mistake, i didnt understood that you want in table with ', I will edit the answer – Zeljka Jun 22 '17 at 12:23
Hi Zeljka, this isn't working for me, below is how the code is currently looking: `$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (`date_time`, `server_name`, `carried_out_by`, `verified_by`, `authorised_by`, `work_carried_out`, `work_verified`, `change_reason`, `perceived_impact`, `rollback_process`) values ('\'$datetime\'','\'$servername\'','\'$carriedoutby\'','\'$workverifiedby\'','\'$authorisedby\'','\'$escaped_string\'','\'$howverified\'','\'$reason\'','\'$impact\'','\'$rollback\'')");` – Harry Jun 22 '17 at 13:15
somethings wrong but I cant see what, only difference between your and mine query is (`date_time`, `server_name` etc..) mine query is $stmt = $db->prepare("INSERT INTO zeljka (`name`,`lastName`) VALUES ('\'$st\'', '\'$sta\''); and it works ://// – Zeljka Jun 22 '17 at 13:42

score -1 · Answer 4 · answered Jun 22 '17 at 11:10

-1

You can use like this :

$stmt = $db->prepare("INSERT INTO [dbo].[server_log_entries] (work_carried_out)
                    values ('$workcarriedout')");

answered Jun 22 '17 at 11:10

Ankesh Vaishnav

105
1
5

Hi Ankesh - thanks for the comment, this still gives me the same issue where data with single quotes in won't insert into the SQL table. – Harry Jun 22 '17 at 11:29

AJAX won't insert data with single quotes into SQL

4 Answers4