JavaScript error in Parameterized query

Question

Here is what I am trying to do (for over a day now :( A user clicks on a link of a book name and I read the name of that book. I then take that book name and make an Ajax request to a Jersey resource. Within that Jersey resource, I call a method in a POJO class where one method interacts with database and gets the data to be sent back to a Jersey resource. I have got many errors but I have been able to fix them one at a time. The error currently I am stuck at is:

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 '?' at line 1

Here is my JavaScript code:

function dealWithData(nameOfBook){

    var bookName = encodeURI(nameOfBook);

    console.log("http://localhost:8080/library/rest/books/allBooks/"+bookName);

    var requestData = {

            "contentType":  "application/json",
            "dataType":     "text",
            "type":         "GET",
            "url":          "http://localhost:8080/library/rest/books/allBooks/"+bookName

**//beforeSend has been added as an edit to original code**

beforeSend: function (jqXHR, settings) {
              var theUrlBeingSent = settings.url;
              alert(theUrlBeingSent);
}
    };

    var request = $.ajax(requestData);

    request.success(function(data) {

        alert("Success!!");
    });

    request.fail(function(jqXHR, status, errorMessage) {
        if((errorMessage = $.trim(errorMessage)) === "") {
            alert("An unspecified error occurred.  Check the server error log for details.");
        }
        else {
            alert("An error occurred:  " + errorMessage);
        }
    });
}

For some reason in above code, console.log line shows url with spaces being encoded as %20 while in the variable 'requestData', url doesn't have that encoding. I am unable to understand why.

Here is the code for my resource:

@GET
    @Path("/allBooks/{bookName}")
    @Produces(MediaType.APPLICATION_JSON)
    public Response getBook(@PathParam("bookName") String bookName){

        System.out.println("Book name is: "+ bookName);
        BookInformation bookInfo = new BookInformation();
        String bookInformation =bookInfo.bookInformation(bookName);

        ResponseBuilder responseBuilder = Response.status(Status.OK);
        responseBuilder.entity(bookInformation);

        Response response = responseBuilder.build();
        return response;
    }

Here is the bookInformation method:

public String bookInformation(String bookName){

        String infoQuery = "Select * from bookinfo where name = ?";
        ResultSet result = null;
        conn = newConnection.dbConnection();    
        try
        {   
            preparedStatement = conn.prepareStatement(infoQuery);
            preparedStatement.setString(1, bookName);
            result = preparedStatement.executeQuery(infoQuery);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        try
        {
            if(result != null){

                while(result.next()){

                    availability = result.getString("availability");
                    isbn = result.getInt("isbn");
                    hardback = result.getString("hardback");
                    paperback = result.getString("paperback");
                    name = result.getString("name");

                }
            }
            else{
                System.out.println("No result set obtained");
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        //I will build this String using a String builder which I will return
String finalBookInformation = information.toString();
                    return finalBookInformation;
    }

Earlier, in dataType I had json which was throwing a different error, but I realized I was not building json so I changed dataType to text and that error went away. My parametirized query doesn't execute. If I try hard coding a value from database, it works fine but not when I use prepared statement. I eventually want to return JSON but for now I just want it to work. Any help will be appreciated. I have tried researching and doing whatever I can but it is not working. Is it the encoding causing the problem? Is it my Ajax call? Any help is appreciated. Thanks.

score 1 · Accepted Answer · answered Jun 14 '15 at 19:58

Seems like issue is in your database query execution please replace the code

preparedStatement = conn.prepareStatement(infoQuery);
preparedStatement.setString(1, bookName);
result = preparedStatement.executeQuery(infoQuery);

with

preparedStatement = conn.prepareStatement(infoQuery);
preparedStatement.setString(1, bookName);
result = preparedStatement.executeQuery();

score 0 · Answer 2 · answered Jun 14 '15 at 06:33

0

You are using HTTP GET method and GET method automatically %20 if find space in url.

If you change your method type to POST It should work find for you.

answered Jun 14 '15 at 06:33

Chetan Verma

735
5
10

If I change GET to POST in my Ajax request and similarly in Resource class, it still gives the same error. – user3044240 Jun 14 '15 at 06:45
please alert the book name before where you are adding it to url and let me know what it prompt – Chetan Verma Jun 14 '15 at 07:02
Name being alerted is the same as text of link being clicked. If my book's name is Harry Potter, then it is alerting it in the exact same format as in the link, Harry Potter – user3044240 Jun 14 '15 at 17:20
you don't think space between "Harry Potter" is producing that %20 ? – Chetan Verma Jun 14 '15 at 17:55
I am not sure. As per your instructions I alerted name of book which was passed to JavaScript function as nameOfBook in my posted code. After alerting, I encode the uri and I think that's what is adding %20 instead of that space. – user3044240 Jun 14 '15 at 18:03
so after removing the space whether the %20 added ?? – Chetan Verma Jun 14 '15 at 18:19
I added a beforeSend in the above Ajax Call to check as to what URL is being sent to server. I alerted the value and this is what I received: http://localhost:8080/library/rest/books/allBooks/The%20Great%20Gatsby So the url does contain the encoding. Now what? – user3044240 Jun 14 '15 at 18:26
Sorry I am unable to understand. Call where? final? – user3044240 Jun 14 '15 at 18:43
"url": decodeURIComponent ("http://localhost:8080/library/rest/books/allBooks/"+bookName) do like this in you request data payload – Chetan Verma Jun 14 '15 at 18:46
now the urll is without %20, but why is it that when I check in the headers, Request URL still shows up with %20? – user3044240 Jun 14 '15 at 19:07
I did not understand -where you are checking the headers ?? on server side?? – Chetan Verma Jun 14 '15 at 19:15
on server side what the value is printed for book i think that is the place where you can handle your exception?? – Chetan Verma Jun 14 '15 at 19:20
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/80529/discussion-between-chetan-verma-and-user3044240). – Chetan Verma Jun 14 '15 at 19:24

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

You can use HTTP GET but it will encode the URL as you discovered. You will need to decode the URL on the server-side. For how to do that, take a look at: How to do URL decoding in Java?.

edited May 23 '17 at 11:43

Community

1
1

answered Jun 14 '15 at 06:34

Cymen

14,079
4
52
72

When I check in the bookInformation method, bookName being passed is without any %20. Code is able to reach the correct resource, as the bookInformation method is within that resource. I think it means that it did reach the correct url. Am I thinking and understanding this correctly or am I totally wrong? – user3044240 Jun 14 '15 at 06:49
I can see now after adding beforeSend in my above JavaScript that the URL being sent to server is encoded. You were correct. But in the example link that you have given, it already has String url. How will I access the URL in my Java class in that bookInformation method? Also, how will this decoding help me in solving the error that I am receiving. Me confused! – user3044240 Jun 14 '15 at 18:36

JavaScript error in Parameterized query

3 Answers3