using variables in sql query in php

Question

So I'm building a website and i need to access a table which holds the information about products

I'm using to navigate to the page

<a href="productDetails.php?table=FeaturedProducts&id=1" >

then in products details page I'm using this to run the php query

 <?php   

require "connection.php";

$table = $_GET["table"];
$id = $_GET["id"];

$sql = "select * from '.$table.' where ID  = '.$id.'";

$result = mysqli_query($conn, $sql);  
                  $row = mysqli_fetch_array($result);
    
    
    $pname= $row['Product_name'];



?>

this doesn't seem to work please tell me how i can do this.

Does this answer your question? [Selecting data from SQL Server Using PHP](https://stackoverflow.com/questions/51812304/selecting-data-from-sql-server-using-php) — executable, Sep 23 '20 at 09:16
Ref: [Bobby Tables](https://xkcd.com/327/) or more formal: [SQL Injection](https://owasp.org/www-community/attacks/SQL_Injection). So please do a lot of reading before you continue this. — Yoshi, Sep 23 '20 at 09:17
Remove all single quote chars and dots from the query text. Or backward replace outer dquote chars with single quotes. — Akina, Sep 23 '20 at 09:18
I would recommend reading up on strings (and how concatenation works) [in the manual](https://www.php.net/manual/en/language.types.string.php). This is a fundamental part of PHP that you need to have locked down, or you'll run into more issues along the way. However, in this case, you should rather use parameterized [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of using completely unescaped user data directly in your queries like that. _Never ever ever never_ trust user input. — M. Eriksson, Sep 23 '20 at 09:23
As others have said, this is completely the wrong way to build a query. Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Sep 23 '20 at 09:29
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped input values. — ADyson, Sep 23 '20 at 09:29
P.S. you can't parameterise table names, so you need to whitelist those yourself instead. — ADyson, Sep 23 '20 at 09:29

score 1 · Accepted Answer · answered Sep 23 '20 at 09:26

1

You made mistake in your concatenation of string. Take a look to your code here :

$sql = "select * from '.$table.' where ID  = '.$id.'";

You try to concatanate the $table and $id variable. (we agree it's a SQL Injection problem).

But PHP will interpret the string result like this : select * from '.FeaturedProducts.' where ID = '.1.'

So you have the ' are not necessary in your code for the table name, and it's add point to your values. Because MySQL does to give you error message.

So your correct code will be (and make modification for use prepare statement to avoid SQL Injection) :

$sql = "select * from $table where ID  = '$id'";

answered Sep 23 '20 at 09:26

Inazo

488
4
15

Be careful though, this allows SQL injection. You should escape the variables before putting them in the database – Timberman Sep 23 '20 at 11:22
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Sep 23 '20 at 11:31
Yes it's necessary to use modern SQL function for prepare the statement. – Inazo Sep 23 '20 at 12:29

using variables in sql query in php

1 Answers1