How to detect attempted intrusion?

Question

In order to protect against SQL injection, I need to elaborate a function in which I eliminate the possibility of someone trying to act in bad faith.

The question is, mysqli brings with you some functionality to help you go against it? And besides, what would be the best way, that is, the best function to account for eliminating the possibility of SQL Injection?

Answer 1

Preventing attacks is sometimes easy, and sometimes difficult, depending on who is creating these barriers. There are currently several libraries of the type Open Source through the internet, able to deal with a large part of the attacks so far elaborated.

As for functions/libraries "pattern" if I may say so, currently there are only the Mysqli and the PDO.

Mysqli or Mysql improved, is an improved version of Mysql, currently with style support object-oriented and procedural , both effective.

PDO or PHP Data Objects is an extension that allows connecting to databases, in addition to providing abstraction of these layers. The PDO by default supports multiple databases.

Which to use, is something to be chosen because it will program, because both have several utilities, and sometimes, depending on the situation, it is easier to work with each other.

As to injection of SQL, both come well prepared for these types of attacks.

Both the Mysqli like the PDO possess Prepared statments and stored procedures, effective and greatly reduce the risk of having a injection of SQL.

Prepared Statments & Stored Procedures:

• Query is passed only once, and can be executed several times, with different parameters.
• When the Query is prepared, it is compiled and optimized to run.
• The higher the Query, the longer the time to compile and optimize.
• The parameters do not need to "quotation marks", are automatically processed by the driver.

Example PDO:

<?php
/* Connect to an MySQL database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=localhost';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

With the PDO normally the class should be instantiated in a block Try/catch, to capture the exception in case there is any.

Mysqli example:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$city = "Amersfoort";

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();
?>

Using these drivers does not make you shielded against malicious people, because depending on what you do in your connection/search/insert script, if you don’t know how to handle the input data (data normally sent by the user from forms, search fields, etc.) you’ll always have these problems.

The Developer can be sure that no SQL Injection will occur (However, if other portions of the query are being built up with unescaped input, SQL Injection is still possible).

The other thing is... no matter how hard you try, because if someone really wants to cheat your system, they’ll just do it, the important thing is to put a level of security for the kind of system you want to use, because it’s kind of illogical, imagine - protect a children’s game establishment frequented by only 5-year-olds, with the safety of an adult game house.

If you want security, and reliable scripts, there are several out there, created and evaluated by professionals, and they are Open Source, just remember this.

Some References:

• Prepared Statments & Stored Procedures - PHP.net
• PDO Construct - PHP.net
• Mysqli Prepared Statments - PHP.net

Answer 2

The recommended way to access databases is by using mysqli or PDO, in accordance with documentation.

In the case of mysqli, the function prepare allows the use of Prepared statements, where the consultation (query) is sent to the server separately from the parameters or variables.

Example based on documentation:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$cidade = "Sorocaba";

if ($stmt = $mysqli->prepare("SELECT Estado FROM Cidade WHERE Nome=?")) {  
    $stmt->bind_param("s", $cidade);
    $stmt->execute();
    $stmt->bind_result($estado);
    $stmt->fetch();

    printf("%s fica no estado de %s\n", $cidade, $estado);

    $stmt->close();
}

$mysqli->close();
?>

Remember that SQL Injection is just one of the possible types of data-driven attack. Any and all data received from the user needs to be checked and "escaped" according to the context, for example:

• If using data to execute commands in the system, the attacker can gain access to the system, so "escape" control characters.
• If you display user data on an HTML screen, "escape" special characters, otherwise the user can inject a script and collect data from other users.
• If you write data to files, for example a log, you need to be careful because a malicious user can inject additional lines into the log.

Finally, it is important to have a clear view of what is done with user data to identify potential hazards.