Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Greetings,

I am currently facing a problem that has persisted time and time again, I'm trying to initialize my php website to fetch data from my database and display it on the website, however when I try to run it I am met with the following fatal error:

Fatal error: Uncaught mysqli_sql_exception: 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 'FROMproduct' at line 1 in E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\database\Product.php:16 Stack trace: #0 E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\database\Product.php(16): mysqli->query('SELECT * FROMpr...') #1 E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\functions.php(16): Product->getData() #2 E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\header.php(24): require('E:\\XAMPP\\htdocs...') #3 E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\index.php(3): include('E:\\XAMPP\\htdocs...') #4 {main} thrown in E:\XAMPP\htdocs\youkneadedmebakedgoods\youkneadedme\database\Product.php on line 16


Below is my code:
<?php

//Use to fetch product data
class Product
{
    public $db = null;

    public function __construct(DBController $db)
    {
        if (!isset($db->con)) return null;
        $this->db = $db;
    }

    //fetch product data using getData method
    public function getData($table = 'product'){
        $result = $this->db->con->query("SELECT * FROM{$table}");

        $resultArray = array();

        //fetches product data 1 by 1
        while(mysqli_fetch_array($item = $result, MYSQLI_ASSOC)){ /*<-- resulttype: */
            $resultArray[] = $item;
        }

        return $resultArray;
    }
}


The function that I used to try and print the database data also calls an error:

<?php

//require MySQL Connection
require('database/DBController.php');

//require Product Class
require('database/Product.php');

//DBController object
$db = new DBController(); /* <-- can't for the life of me figure out why it calls an error
                           update: fixed it after 2 painful days, turns out I'm just an idiot*/

//Product Object
$product = new Product($db);

print_r($product->getData()); /* <--another error */


What I have tried:

I have tried browsing google and forums for people who might have encountered the same problems as I have as well as looking for different ways to mitigate the problem or at least look for the correct syntax but to no avail.

I apologize for I am completely new to the backend programming scene and wish to be enlightened more. As such, I am asking for your assistance and would be grateful for any help. Thank you
Posted
Updated 22-May-22 19:36pm

Take a closer look at the error message.
...for the right syntax to use near 'FROMproduct'

The bolded text is the hint you need to be looking at the FROM clause in your SQL statement.
PHP
$result = $this->db->con->query("SELECT * FROM{$table}");

Since PHP replaces the "{$table}" with your table name, "product", you get a FROM clause that reads "FROMproduct".

Well, that's not valid SQL. The SQL language depends on spaces to separate statement items, so you're missing a space after "FROM":
PHP
$result = $this->db->con->query("SELECT * FROM {$table}");
                                              ^
                                              |
                                              +-----missing space
 
Share this answer
 
v2
Comments
Allan Emmanuel Cerujales 23-May-22 3:39am    
Thank you Dave, that worked splendidly. I've been pulling my hair out for hours until this was pointed out. :)
To add to what Dave has said, that's probably not a good idea to do anyway, as it uses string concatenation - so unless you have absolute control over the content of your $table variable and no use input is or can be involved your whole DB is at risk every time you use it.

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

In this case, it would be pretty easy to restrict the values to specific table names only, but if you don't then you are very much at risk.
 
Share this answer
 
Comments
Allan Emmanuel Cerujales 23-May-22 3:38am    
Thank you so much for this, it is very insightful and will practice this with my project as well as research further in order to secure my databases from potential SQL injection attacks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900