Is it possible to pass a "PHP" variable that is passed by an URL as comparison element for a SQL query?

Question

I've set a local server using PHPMyAdmin, and I'm presenting some dynamic data that is stored in that server using some PHP, HTML and SQL. The problem is that whenever I pass a variable that is stored using $variable = mysqli_real_escape_string($conn, $_GET["variable"]); and then I use that variable in a SQL query similar to this one $sql = 'SELECT * FROM assets WHERE variable="$variable";. The array that is generated is empty.

When I do a print_r($variable);, I get the variable that the code is expecting, so I'm not sure why the query sends an empty array. Then, when I hardcode the SQL query with the value of print_r($variable), the correct array is obtained from the query.

Code in PHP that is not working

$variable = mysqli_real_escape_string($conn, $_GET["variable"]);

  print_r($_GET["location"]);

  // make SQL
  $sql = 'SELECT * FROM assets WHERE variable="$variable"';

Where $conn = mysql_connect('localhost', 'user', 'password', 'table'); The connection is correct though

then for example when I hardcode it using the result I get from

print_r($_GET["variable"]); prints N1 on the screen

This PHP is working, but it won't be dynamic

 $sql = 'SELECT * FROM assets WHERE variable="N1';

I'm expecting to see all the results were the field variable = to a $_GET["variable"], where $_GET["variable"] is stored in $variable, but all I'm getting is an empty string.

You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). **This will take care of any pesky quoting issues that may occur.** — aynber, Aug 08 '19 at 18:13
In order to use mysqli_real_escape_string you have to have establish the connection to your database first — Funk Doc, Aug 08 '19 at 18:18
yeap, I'm doing that in a different file that is included at the top `$conn = mysqli_connect('localhost', 'user', 'password', 'table'); // check the connection if(!$conn){ echo 'Connection error: ' . mysqli_connect_error(); }` — Walter Lam Astudillo, Aug 08 '19 at 18:22
Copy past this and will work "SELECT * FROM assets WHERE variable='$variable'"; — Var Yan, Aug 08 '19 at 18:33

score 0 · Answer 1 · answered Aug 08 '19 at 18:18

0

You could use a prepared statement and binding param (for this you don't need the real string escape id done by the msqli prepared and binding)

$conn= new mysqli('localhost', 'user', 'password', 'your_db');
$myVar =  $_GET["location"]; 
$sql = 'SELECT * FROM assets WHERE variable=?';

$query = $conn->prepare( $sql);
$query->bind_param('s',$myVar);
$result = $query->execute();

answered Aug 08 '19 at 18:18

ScaisEdge

131,976
10
91
107

Hi @scaisEdge, thanks for the help. I'm connecting to the localhost, and then I have this: ` $location = mysqli_real_escape_string($conn, $_GET["location"]); $sql = 'SELECT * FROM assets WHERE location="$myVar"'; $result = mysqli_query($conn, $sql); // fetch the resulting rows as an array $myVarArray = mysqli_fetch_all($result, MYSQLI_ASSOC); // free result from memory mysqli_free_result($result); // close the connection to the database mysqli_close($conn); ` – Walter Lam Astudillo Aug 08 '19 at 18:27
will this substitute the fetching, freeing the memory and closing the conn? sorry i'm a bit new to php and SQL – Walter Lam Astudillo Aug 08 '19 at 18:29
you comment have not sense .. to me .. anyway don't use php var in sql ( even though they have been cleaned) use .. prepared statement and binding param for a clean and sqlinjection free code .. – ScaisEdge Aug 08 '19 at 18:30
the you can free memory and close the connection but is not thist the point -- .. anyway .. try and check for the correct result in $result .. content – ScaisEdge Aug 08 '19 at 18:30
Thank you @scaisEdge, after reading more about SQL and PHP functions, I had one command that was "clearing" the array. Thanks for the help :) – Walter Lam Astudillo Aug 08 '19 at 19:33

score 0 · Answer 2 · answered Aug 08 '19 at 18:28

0

Try this code may be solve issues.

   $conn= new mysqli("localhost","my_user", "my_password", "world");
   $sql = 'SELECT * FROM assets WHERE variable='.$_POST["variable"];
   mysqli_query($conn,$sql);

answered Aug 08 '19 at 18:28

Kamlesh Jain

68
6

Not a bad idea, but my problem was that for some reason my array while passing a variable (or maybe at every time) was being cleared. Thanks for the help though :) – Walter Lam Astudillo Aug 08 '19 at 20:05

Is it possible to pass a "PHP" variable that is passed by an URL as comparison element for a SQL query?

2 Answers2