To prevent such an attack, you should validate all data to be used in queries and perform typecasting whenever possible. In addition, you can use a PHP function like mysqli_real_escape_string(), which makes the data safe to use. in queries.
As an alternative is to use Prepared Statements which were introduced to MySQL in version 4.1 and available to PHP in version 5 through the Improved MySQL extension. Without prepared statements, the entire query, including the SQL syntax and the specific values, is sent to the MySQL parser and executed as one long string. With a prepared query, the SQL syntax is sent to the MySQL parser first. Then the specific values are sent separately. MySQL then assembles the query using those values and then executes it.
Prepared statements can be created out of any SELECT, INSERT, UPDATE, or DELECT query. Start by structuring your query, marking placeholders using question marks. An example is shown below:
Take a query such as:
$q = "SELECT first_name, last_name, email FROM customers WHERE customer_id=$cust_id";
As a prepared statement, this query becomes:
$q= "SELECT first_name, last_name, email FROM customers WHERE customer_id=?";
Next, prepare the statement in MySQL:
$stmt=mysqli_prepare($dbconnection, $q);
At this point, MySQL will parse the query for valid syntax, but won't execute it.
Next, bind the PHP variables to the query's placeholders:
mysqli_stmt_bind_param($stmt, 'i', $cust_id);
The i part of the command indicates what kind of variable, integer in this case, to expect.
The table below shows the characters to use when binding variables:$q = "SELECT first_name, last_name, email FROM customers WHERE customer_id=$cust_id";
As a prepared statement, this query becomes:
$q= "SELECT first_name, last_name, email FROM customers WHERE customer_id=?";
Next, prepare the statement in MySQL:
$stmt=mysqli_prepare($dbconnection, $q);
At this point, MySQL will parse the query for valid syntax, but won't execute it.
Next, bind the PHP variables to the query's placeholders:
mysqli_stmt_bind_param($stmt, 'i', $cust_id);
The i part of the command indicates what kind of variable, integer in this case, to expect.
Letter | Represents |
---|---|
d | Decimal |
i | Integer |
b | Blog(binary data) |
s | All Other Types |
No comments:
Post a Comment