通过php将帖子写入postgres，托管在heroku上

Question

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);
    }
?>

Answer 1

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).