Let's say I have a website which sells instruments. I am using PHP and MySQL in order to dynamically build the products page labelled products.php.
Now my table has multiple columns, such as ParentCategory and the like. Pressing on the products tab would take you to category.php, where there will be some categorical buttons you can press on, which will take you to products.php and only show you the relevant products.
Now here enlies the problem. To retrieve the items from the database, we need to set a query like
SELECT * FROM productlist WHERE ParentCategory=1
From the category page, the buttons are wrapped in a form, and each button submits a variable called query using POST. The value would be something like ParentCategory=1.
The full SQL query, with the tablename, is hidden, however the multiple subqueries that the buttons have to submit, such as ParentCategory=1 or ParentCategory=2 are displayed in the HTML of the page itself.
What I want to know is, is this a massive security flaw? As in, could a malicious user exploit such information for hacking purposes? Personally I couldn't think of a way a user could exploit the site after only knowing its column names, but I'm also only starting out in web development.
If that's the case, then what's the safest or universal way that these queries are passed? I would have thought GET but that lengthens the URL and opens the problem of injection up, since any values can be passed.
is this a massive security flaw
YES.
It's not about showing SQL on the client side, it's that you're taking SQL from the browser as is and execute it on your server. That's SQL injection by definition. The browser isn't really limited to the SQL snippets you put into your HTML; the browser (read: any HTTP client) could send any freeform SQL back to your server. If your server just executes any given SQL from any arbitrary client, you're dead.
Take some defined parameters and translate them into SQL. E.g.:
if ($_GET['order'] == 'product') {
$sql .= 'ORDER BY products.id';
}
First of all to be clear, what you've called "small part of sql query" called http query string, and its passed through GET request, and available in PHP via $_GET array, so it is not technically part of your SQL query until you pass it to your SQL query.
To answer your question, - It is safe if you sanitize variables, for PHP you have to use mysqli_real_escape_string function, take a look here https://secure.php.net/manual/en/mysqli.real-escape-string.php
If you have to use PDO then use PDO::prepare which is automatically quote variables content to prevent SQL injection. https://secure.php.net/manual/en/pdo.prepare.php
That looks like a bad idea to me, if you're planning on pasting the text from the form directly onto the end of your "where" clause. If you're doing a POST with some form value set to "ParentCategory=1", someone could as easily do SQL injection via a POST as via a GET (see Is SQL Injection possible with POST? for more info).
A better way to do this is something like this:
Your PHP Code constructs a query that maps your form parameter to the name of the database column:
SELECT * FROM productlist WHERE parent_category=2
Send the query constructed in PHP to the database.