What is the question mark in a query?
Using ? in a query really avoids SQL injection ? Avoid 100%? I saw this code and heard a lot of people talking about it, saying what helps in this case and how to use? Could anyone give a better example?
$query = "SELECT * FROM tabela WHERE username = ? OR username = ?";
2 answers
This is a placeholder for a parameterized query (parameterized query ).
Is an anonymous parameter to the query. it is used to avoid SQL Injection even, but not only . With it you can not inject parts of a query into your query.
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 fine 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 happens to contain queries.
One of the ways to use would be:
$stmt = $mysqli->prepare("SELECT * FROM tabela WHERE username = ? OR username = ?");
$stmt->bind_param('ss', $userName, $userName);
In this way there is The Binding of the PHP $userName variable to the two parameters that the query SQL is waiting for.
This is a feature available in the Bank of data . This is called prepared query . is originally used to speed up the execution of the query. With it a queryis cached by the database planner allowing some optimizations since only the parameters are different but the execution follows the same scheme always. In some cases the gain can be considerable.
As a result you gain greater security against SQL injection . But it is important note that security comes more for a side effect of the feature. You prevent something like this from happening without the programmer noticing:
$query = "SELECT * FROM tabela WHERE username = $userName";
Suppose someone types and $userName gets "" OR 1 = 1; DROP TABLE tabela;. Okay, your table went into space.
This prepared query feature is made available by the PHP API mysqli 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 don't have to use only mysqli. If the database and the PHP API that makes the Access provide the feature, you can use it in a standardized way. So it's not something PHP does for you, it comes from the database.
Just to supplement:
$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 how the parameter is used, you put more meaning than it is passing through and where it is used. I'm not saying that PDO is necessarily better. In some cases the syntax may be slightly different.
MySQL SQL syntax if you are going to use the PHP independent feature or want to better understand the feature.
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 it to the run database, and cross site scripting (XSS) defects arise when we take user text and pass it to the browser as if it were HTML (and that HTML can contain script tags).
In an ideal world, instead of using strings to represent both the query and the user data (allowing data and sql code to be concatenated / interpolated dangerously) we would use separate types for these values that forbid mixing with each other.
Okay, but then, how to program to avoid problems with injection?
A first alternative is to offer an "escape" function, which converts user data into secure code. For example, in HTML we convert all & to & and in SQL we protect the quotes. Use a function of escape 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 is not idempotent.
The second alternative in the case of SQL is to parameterize the query. Instead of coming out concatenating things in hand you say (with the ? or some similar syntax) where are the "gaps" of the query and the bank itself fills those gaps with the parameters, which are passed separately. As user data they are passed to the bank as parameters, there is no risk of them being executed as SQL code.