-1

Solved: Problem was using htmlentities() when getting the value from my form which changes '>' and '<'

< becomes &lt
> becomes &gt

which is not recognized by sql and thus causing my problem!

I wrote a basic website in HTML and PHP on c9.io that can send queries to an SQL DATABASE. Most queries I send work fine so long as they do not use greater than or less than operators which result in a syntax error. For example:

SELECT bTitle FROM Book WHERE bQuantity > 9;

Results in the error:

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

But if I simply use the equal operator it works fine.

SELECT bTitle FROM Book WHERE bQuantity = 10;

Not sure what I'm doing wrong or overlooking but would appreciate any input.

-edit- The code excerpt which creates the Book table which I am querying.

CREATE TABLE Book
(
   bID INT,
   bTitle VARCHAR(200),
   bPrice DECIMAL,
   bAuthor VARCHAR(200),
   bQuantity INT,
   supplierID INT,
   subjectID INT
);

Here is where my site takes the query input

<form action="result.php" method="get" target="resframe">
   <label for="query_text">Enter Query:</label>
   <input type="text" id="query_text" name="query_text"/>
   <input type="submit" name="submit" value="send"/>
</form>

And my result.php actually makes a query to the database here using the following code which works for so far all queries except those using '<' or '>' or a variation of '>=' '<='

$val1 = htmlentities($_GET['query_text']);
$results = mysqli_query($connect, $val1);
Seth
  • 1
  • 2
  • what if you try `>=` greater than or equal to? `SELECT bTitle FROM Book WHERE bQuantity >= 9; ` –  Dec 05 '16 at 07:50
  • What is bQuantity column datatype? – anon Dec 05 '16 at 07:53
  • That query is syntactically valid. Copy and paste it exactly as is into a MySQL console and it'll work (assuming the right tables and column exist etc.). Something we're not seeing is going on here… – deceze Dec 05 '16 at 08:38
  • column is type int and as far as I can tell the query is right, even when I put an echo right before I send it to the server it is correct but for some reason it will not accept any comparison with a '>' or '<' operator in it – Seth Dec 05 '16 at 13:36
  • @deceze♦ It is syntactically correct but I am still being told otherwise by SQL. I included more info of I think the few places that could be causing the problem – Seth Dec 06 '16 at 02:52
  • 1
    Do. not. HTML-escape. The. Query! Why are you doing that in the first place? – deceze Dec 06 '16 at 06:23
  • @Seth, Seems like the front-end is created to make the user "write" the entire query directly and then the text provided by the user is being filtered for special characters. Whilst your idea of escaping the special characters is right, the way it's been implemented is not. You may ask the user to input the value that you'd like to be used in the query for "bQuantity", and then escape the variable which accepts that input. Use this filtered input in a prepared query in the "code" or better yet consider having a look at PDO. – Dhruv Saxena Dec 06 '16 at 19:21
  • 1
    @deceze♦ @DhruvSaxena2 Thank you guys, works fine without htmlentities(). I am new to html and php and didn't have a correct understanding of how htmlentities worked, but I see how that was wrong to even be in there in the first place. – Seth Dec 06 '16 at 22:10

1 Answers1

2

Looks like that you are using the function htmlspecialchars() for your query. That's the only possible explanation. echo your question out for the proof.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345