What is the question mark in a query?

Question

Use ? in a query really avoids the SQL Injection? Avoids 100%? I saw this code and I heard many people talking about it, saying it helps in this case and how to use it? Could someone give a better example?

$query = "SELECT * FROM tabela WHERE username = ? OR username = ?";

Answer 1

This is a placeholder for a parameterized query (parameterized query).

Is an anonymous parameter for the query. It is used to prevent SQL Injection same, but not only. With it you can’t inject parts of a query within your query.

It would be something like using "SELECT * FROM tabela WHERE username = $par1 OR username = $par2" but it has the advantage that it is controlled by the database through the PHP API rather than purely by PHP, so it is much safer. The database shall have the query ready waiting for the parameter. Not a string with the query. The SQL Injection occurs because of the manipulation of strings which, incidentally, contains darlings.

One of the ways to use it would be:

$stmt = $mysqli->prepare("SELECT * FROM tabela WHERE username = ? OR username = ?");
$stmt->bind_param('ss', $userName, $userName);

This way there is the linking of the $username variable of PHP to the two parameters that a query SQL is waiting.

This is a resource available in the database. This is called Prepared query. Originally used to expedite the execution of query. With him a query is curly by the database planner allowing some optimizations since only the parameters are different but the execution always follows the same scheme. In some cases the gain may be considerable.

As a consequence you gain greater security against SQL Injection. But it is important to note that security comes more by a side effect of the resource. You prevent something like this from happening without the programmer noticing:

$query = "SELECT * FROM tabela WHERE username = $userName";

Suppose someone typed and $userName receive "" OR 1 = 1; DROP TABLE tabela;. Ready, your table has gone to space.

This feature of Prepared query is made available by the API mysqli PHP and is used as indicated above.

I used the Mysql example which is the most used database with PHP but can be used with any other database that provides some form of "prepared queries". So you don’t just need to use the mysqli. If the PHP database and access API provide the resource, it is possible to use it in a standardized way. So it’s not something PHP does for you, it comes from the database.

Just to complement:

$stmt = $pdo->prepare("SELECT * FROM tabela WHERE username = :user OR username = :user");
$stmt->bindParam(':user', $userName);

I put in the Github for future reference.

This is the PDO where you have more control over how the parameter is used, you place more meaning than what you are going through and where it is used. I’m not saying that PDO is necessarily better. In some cases the syntax may be a little different.

Reference.

Mysql SQL syntax whether to use the PHP standalone feature or want to better understand the feature.

Answer 2

Code injection problems can occur whenever you mix two separate languages. For example, SQL injection bugs occur when we treat user data as SQL code and pass to the database run and XSS defects (cross site scripting) arise when we take user text and pass to the browser as if it were HTML (and this HTML can contain script tags).

In an ideal world, instead of using strings to represent both the query and user data (allowing data and sql code to be concatenated/interpolated dangerously) we would use separate types for these values that prohibit mixing with each other.

All right, but then how to program to avoid injection problems?

A first alternative is to offer an "escape" function, which converts user data into secure code. For example, in HTML we convert all & in & and in SQL we protect the quotation marks. Using an escape function is a fairly simple and common approach but you have to be careful not to escape something twice unintentionally, since this operation is usually not idempotentive.

The second alternative in the case of SQL is to parameterize the query. Instead of concatenating things at hand you say (with the ? or some similar syntax) where are the "gaps" of the query and the database itself fills these gaps with the parameters, which are passed separately. As user data is passed to the database as parameters, there is no risk of them being executed as SQL code.