Basic SQL Help - Syntax error in Query Expression

1

Hello all,
I am beginning to learn SQL and I have made a basic .mdb table. I am trying to have a page where the user selects a book category and when they click on it, the next page will have the book content & author within the book category they chose. I currently only have 1 book and author listed per category to keep it simple for now.

Initially the server was selecting the very first book within the database for all book category selections, so I tried to incorporate the user chosen category, but now I am getting an error. The error is: "Fatal error: Uncaught exception 'com_exception' with message Source: Microsoft Office Access Database Engine
Description: Syntax error (missing operator) in query expression 'author='.' in E:\server\Database\moredetails2.php Stack trace: #0 E:\server\Database\moredetails2.php(20): com->Execute('SELECT Books, C...') #1 {main} thrown in E:\server\Database\moredetails2.php on line 20"

My code for the page is:

<?php $authorid=filter_input(INPUT_GET, "author");

Source=../Database/poe.mdb"; //creates the connection object and define the connection string

$conn->Open($connString);

$selectCommand="SELECT Books, Content FROM Authors WHERE author=$authorid;";

$rs = $conn->Execute($selectCommand);
//opens a recordset from the connection object
if (!$rs->EOF){
$BookName=$rs->Fields("Books");
$AuthorName=$rs->Fields("Author");
$Content=$rs->Fields("Content");
}

print "Book: $BookName<br>"; print "Author: $AuthorName<br>"; print "$Content<br><br>";

$rs->Close;
?>
I cut out the initialize bits, but I can add them if that is helpful.

2

is "$authorid" defined, and contains a value, at that point? If not the actual SQL executed [verbatim] will be

SELECT Books, Content FROM Authors WHERE author=$authorid;

without any substitution for $authorid, which won't be valid!

My first action would be to run the SQL

SELECT Books, Content FROM Authors WHERE author=123

against the database and see if I get an error, zero rows returned, or the row that matches Author 123 (if that value exists - doesn't matter whether it does, or not, for this test, but if you can use an actual ID value so much the better)

That will tell you if you have your SQL syntax correct or not - you might have misspelled a column name or somesuch.

If that works OK then I would put some sort of "PRINT" statement in PHP to display the exact value of $selectCommand before you execute the SQL. Perhaps "$authorid" contains some rogue characters and after substitution in $selectCommand the resulting SQL code is not valid. (Maybe there is a debugging tool in PHP which will allow you to see the value of $selectCommand by single-stepping through the code; sorry I'm not familiar with PHP)

One thing to be aware of: if you use this type of "$authorid" substitution (in PHP) you are open to what is called "SQL Injection" where a malicious user can then supply a value for "$authorid" which is not a valid ID number but is, instead, a malicious command - like deleting all the rows from your Authors table, or dropping your database :frowning:

For the purposes of learning about SQL its probably not a concern, but if you progress to building, say, a website which is "public facing" then you will definitely need to change your SQL coding style to avoid that problem. (There are much "smarter" ways to code your query which will perform better, and be "secure"; when you get to that point please come back and ask for advice :slightly_smiling: )