How Does {} Affect a MySQL Query in PHP

How does {} affect a MySQL Query in PHP?

ON the SQL side, there is absolutely no difference : the two queries are exactly the same.

(you can check that by echo-ing them)

{$variable} is a more complete syntax of $variable, that allows one to use :

• "this is some {$variable}s"
• "{$object->data}"
• "{$array['data']}"
• "{$array['data']->obj->plop['test']}"

For more informations, you should read the Variable parsing / Complex (curly) syntax section of the manual (quoting a few bits) :

This isn't called complex because the
syntax is complex, but because it
allows for the use of complex
expressions.

Any scalar variable, array element or
object property with a string
representation can be included via
this syntax.
Simply write the
expression the same way as it would
appear outside the string, and then
wrap it in { and }.

What is the use of Curly Braces in PHP mysql?

TL;DR: NO, IT DOES NOT PREVENT SQL INJECTION

The curly braces are a way to inject variable's content into the string. Reference: https://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.double

So this two commands do exactly the same thing:

query("SELECT * FROM users WHERE email = $email AND password = $password");
query("SELECT * FROM users WHERE email = {$email} AND password = {$password}");

If it's the same, then why bother with curly braces? Because it's a way to inject an object's property/data member into string:

query("SELECT * FROM users WHERE email = {$user->email} AND password = {$user->password}");

By reading the filename retrieve.php and retrieve_safely.php, there's a difference that there's a quote around the variable in _safely file.

So, unless there's a kind of input sanitization before the code in retrieve_safely.php, that file is no more safe than retrieve.php

PHP Page Loads Mid-Query (MySQL)

The below code is referenced from the following post provided by @mickmackusa in an above comment. Strict Standards: mysqli_next_result() error with mysqli_multi_query

if($MySQLi->multi_query($sql)){
    do{} while($MySQLi->more_results() && $MySQLi->next_result());
}
if($error_mess = $MySQLi->error){ die("Error: " . $error_mess); }

This code managed to prevent my next page from loading until all queries were completed as intended.

MySQL indexing has no speed effect through PHP but does on PhpMyAdmin

The mysql documenation says

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

MariaDB documentation Force Index here says this

FORCE INDEX works by only considering the given indexes (like with USE_INDEX) but in addition, it tells the optimizer to regard a table scan as something very expensive. However, if none of the 'forced' indexes can be used, then a table scan will be used anyway.

Use of the index is not mandatory. Since you have only specified one condition - the time, it can choose to use some other index for the fetch. I would suggest that you use another condition for the select in the where clause or add an order by

order by  pair, price, time

MySQL efficient SELECT query

There's not enough information to determine how an item is available. This severely impedes the ability to query item 2.

That said, let's suppose we add a "available" column to the Items table that is a tinyint of 0 for not available, 1 for available.

A query, then, which would get all email addresses for persons watching items that are available is:

 SELECT u.email FROM Users AS u JOIN ItemsToUsers AS k ON k.user_id = u.id JOIN Items AS i on i.id = k.item_id WHERE i.available = 1;

Alternatively, you could use a subquery and IN.

Let's suppose you have a different table called Availability with the columns id, item_id and available, which again is a tinyint containing a 1 for available and 0 for not available.

SELECT u.email FROM Users AS u JOIN ItemsToUsers AS k ON k.user_id = u.id WHERE k.item_id IN (SELECT a.item_id FROM Availability AS a WHERE a.available = 1);

Again, without an idea of how you are getting a list of available products, it is impossible to optimize your queries for retrieving a list of email addresses.

How can I prevent SQL injection in PHP?

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create an SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using PDO (for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
   $stmt->execute([ 'name' => $name ]);
   
   foreach ($stmt as $row) {
       // Do something with $row
   }
   

2. Using MySQLi (for MySQL):

Since PHP 8.2+ we can make use of execute_query() which prepares, binds parameters, and executes SQL statement in one method:

$result = $dbConnection->execute_query('SELECT * FROM employees WHERE name = ?', [$name]);

while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

Up to PHP8.1:

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

---

Correctly setting up the connection

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example, the error mode isn't strictly necessary, but it is advised to add it. This way PDO will inform you of all MySQL errors by means of throwing the PDOException.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

---

Explanation

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

---

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

MySQL integer field is returned as string in PHP

When you select data from a MySQL database using PHP the datatype will always be converted to a string. You can convert it back to an integer using the following code:

$id = (int) $row['userid'];

Or by using the function intval():

$id = intval($row['userid']);

---

Related Topics