im using php to take a song request via an ajax, and save it to a postgres db.
everything is in heroku, and I can connect to the db fine using pgadmin
i get the following error - what's the right way to take the string from the GET request and write to db?
2014-08-24T01:43:32.062128+00:00 app[web.1]: [Sun Aug 24 01:43:31.864706 2014] [proxy_fcgi:error] [pid 64:tid 139700481152768] [client 10.53.42.218:51189] AH01071: Got error 'PHP message: PHP Warning: pg_query(): Query failed: ERROR: syntax error at or near "\" LINE 1: INSERT INTO songs (request) VALUES(\'song\')
^ in /app/songs.php on line 21 ', referer: http://sep21.herokuapp.com/2014-08-24T01:43:32.062130+00:00 app[web.1]: [24-Aug-2014 01:43:31] WARNING: [pool www] child 61 said into stderr: "NOTICE: PHP message: PHP Warning: pg_query(): Query failed: ERROR: syntax error at or near "\""
2014-08-24T01:43:32.062132+00:00 app[web.1]: [24-Aug-2014 01:43:31] WARNING: [pool www] child 61 said into stderr: "LINE 1: INSERT INTO songs (request) VALUES(\'song\')"
2014-08-24T01:43:32.062134+00:00 app[web.1]: [24-Aug-2014 01:43:31] WARNING: [pool www] child 61 said into stderr: "
^ in /app/songs.php on line 21"
code is as follows:
CREATE TABLE songs
(
request character varying[] NOT NULL,
created bigint,
id bigserial NOT NULL,
CONSTRAINT "Pk" PRIMARY KEY (id)
)
$.ajax({
type: "GET",
url: "songs.php",
data: q,
success: function(resp){
// console.log(q)
// console.log(resp)
try{var song_resp = JSON.parse(resp)}
catch(err){var song_resp = err}
// console.log(song_resp)
// console.log(target)
if (song_resp.pass == true){
$("#nice_choice").slideDown(250)
// $("#nice_choice").css("-webkit-animation-play-state","running")
// $("#nice_choice").css("-animation-play-state","running")
}
else {
$("#something_wrong").slideDown(250)
// $("#something_wrong").css("-webkit-animation-play-state","running")
// $("#something_wrong").css("-animation-play-state","running")
}
},
error: function (jqXHR, exception){
$("#something_wrong").slideDown(300)
// $("#something_wrong").css("-webkit-animation-play-state","running")
// $("#something_wrong").css("-animation-play-state","running")
}
}); // Ajax Call
<?php
if ($_GET['q']){
$song = $_GET["q"];
$dbconn = pg_connect("host=ec2-54-247-111-1.eu-west-1.compute.amazonaws.com
dbname=dbname
user=user
password=pw")
or die('Could not connect: ' . pg_last_error());
$result = pg_query($dbconn, "INSERT INTO songs (request) VALUES(\'song\')");
//dump the result object
if ($result == false) {
echo false;
}
else{
echo true;
}
// Closing connection
pg_close($dbconn);
}
?>
Use pg_query_params or preferably PDO. Don't roll your own quoting. Ever. It's just plain wrong and the issues are well documented in the PHP manual.
You should preferably be using PDO.
(The immediate problem is that your quoting is wrong. You've used \' to escape single quotes where no escaping is required, so the backslashes are preserved in the final query string. You can see that in the error message).