Do I insert data right (php+ MySQLi)?

Question

Is the method I use to insert data using MySQLi right? It works but is it safe enough?

    $login = stripslashes($login);
    $login = htmlspecialchars($login);
    $password = stripslashes($password);
    $password = htmlspecialchars($password);

    $login = trim($login);
    $password = trim($password);


 $result2=mysqli_query($db,"INSERT INTO users (login,password) VALUES('$login','$password')");

Answer 1

Whenever you interact with a database inside php you need to keep in mind a checklist :

1. Set up the connection correctly, for example, make sure that when using pdo, set the error mode to exception and tell it not to emulate prepared statements. Use the following code to set the connection attributes :

   $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
   $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   

2. Use prepared statements and parameterized queries when fetching data.

Using mysqli:

    $stment = $dbConnection->prepare('SELECT * FROM users WHERE name = ?');
    $stment->bind_param('s', $name);

    $stment->execute();

    $result = $stment->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row
    }

Using PDO:

    $stment = $pdo->prepare('SELECT * FROM users WHERE name = :name');

    $stment->execute(array('name' => $name));

    foreach ($stment as $row) {
        // do something with $row
    }

The idea behind prepared queries is very simple - the query and the data are sent to the SQL server separately. When we send a database query along with the data, the data can be used to execute a potentially malicious query. For more info, refer this answer.

If you want to dynamically add identifiers and syntax keywords use whitelisting. It basically involves checking if the user input is an expected one, like this :

$orders  = array("name","price","qty"); //field names
$key     = array_search($_GET['sort'],$orders)); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe

3. Sanitize input from the user before inserting/searching the database using functions like mysqli_real_escape_string, if you are not using prepared statements for some reason.
4. Make sure you handle exceptions and errors well.

Answer 2

Use PDO. Considered more secure than the outdated MySqli.

Answer 3

You also can use following function for the same thing. You can prepare your final string which is directly being used in database query with the following function. This function will take care almost all the things which prevent sql injection and clean the harmful or unwanted content from your inputs.

function cleanInputs($data) {
    $clean_input = array();
    if (is_array($data)) {
        foreach($data as $k = > $v) {
            $clean_input[$k] = cleanInputs($v);
        }
    } else {
        if (get_magic_quotes_gpc()) {
            $data = trim(stripslashes($data));
        }
        $data = strip_tags($data);
        $clean_input = trim($data);
    }
    return $clean_input;
}

Things are depends on the requirement you can even modify the definition or logic accordingly.