What is the question mark in a query?

14

Does%% use of a query really prevent SQL injection ? Avoid 100%? I saw this code and I heard lots of 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 = ?";
    
asked by anonymous 26.06.2014 / 23:18

2 answers

16

This is a placeholder for a parameterized query ).

This is an anonymous parameter for query . It is used to avoid SQL Injection , but not only . With it you can not inject parts of a query into your query .

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

One way to use it would be:

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

In this way there is the linking of the PHP $ userName variable to the two parameters that SQL query is waiting for.

This is a feature available in the database . This is called prepared query . Originally used to expedite the execution of query . With it a query is cached by the database planner allowing some optimizations since only the parameters are different but execution always follows the same schema. In some cases the gain can be considerable.

As a result, you gain greater security against SQL injection . But it is important to note that security comes more from a side effect of the feature. You prevent something like this from happening without the programmer realizing:

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

Suppose someone types and $userName receives "" OR 1 = 1; DROP TABLE tabela; . Ready, your table went into space.

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

I used the example of MySQL 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 do not have to use only mysqli . If the database and the PHP API that makes the access provide the resource, you can 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);

This is the PDO where you have more control as the parameter is used, you puts more meaning in what is going on and where it is used. I'm not saying the PDO is necessarily better. In some cases the syntax may be slightly different.

Reference .

MySQL SQL syntax if you use the feature independent of PHP or want to better understand the feature.

    
26.06.2014 / 23:28
3

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

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

Okay, so how do you plan to avoid injection problems?

A first alternative is to provide an "escape" function, which converts user data into secure code. For example, in HTML we convert all & to & 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 miss something twice without meaning to, as this operation is usually not idempotent.

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

    
26.06.2014 / 23:53