How can I change my SQL statements to be prepared (and secure) statements?

Question

Basically I want to protect myself from SQL injections. I have tried searching online and watching videos but cannot understand exactly what I have to change because as far as I can tell, everyone does it a little bit differently. Any help is appreciated!

?php
// Create connection
$con = mysqli_connect("IPAddress","User","Password","DBName");
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

$sql = "INSERT INTO Email_Subs (email)
VALUES ('$_POST[email]')";

if ($con->query($sql) === TRUE) {
    echo "You have successfully subscribed!";
} else {
    echo "Error: " . $sql . "<br>" . $con->error;
}

$con->close();
?>

Unless you already ventured too far. Consider reading up on [PDO](https://secure.php.net/manual/en/book.pdo.php). Much less overhead for parameterization than the crude mysqli_ API. — mario, Mar 08 '19 at 17:12

MimoudiX · Accepted Answer · 2019-03-08T17:12:30.230

1

You must first use new mysqli() instead of mysqli_connect() to avoid any error in the next php versions

    <?php 

    /* CONNECTION */
    $database_connection = new StdClass();

    /** MySQL hostname */
    $database_connection->server = 'localhost';

    /** MySQL database username */
    $database_connection->username = 'root';

    /** MySQL database password */
    $database_connection->password = '';

     /** The name of the database */
     $database_connection->name = 'yourdatabasename';

     /* ESTABLISHING THE CONNECTION */
    $database = new mysqli($database_connection->server, $database_connection->username, $database_connection->password, $database_connection->name); 

            if($database->connect_error) {

                echo 'connection failed';
            }

   ?>

Then do smething like this :

$stmt = $database->prepare("INSERT INTO Email_Subs (email) VALUES (?)"); 
$stmt->bind_param("s", $_POST[email]);
$stmt->execute();
$stmt->close();
$database->close();

edited Mar 08 '19 at 17:12

answered Mar 02 '19 at 14:08

MimoudiX

612
3
16

Great! That helped a lot, and works perfectly! Is this secure from injections now? – Mar 02 '19 at 14:16
yeah mate, how about a vote and a check mark for my answer ? – MimoudiX Mar 02 '19 at 14:18
I checked, but my votes don't count since my reputation is not high enough yet haha. Also, how can I edit this to echo "You have subscribed!" ? – Mar 02 '19 at 14:19
just add your prepared statement code inside an "isset post" then add the echo before the isset close – MimoudiX Mar 02 '19 at 14:24
[https://stackoverflow.com/questions/17452614/php-mysqli-prepared-statements-login-and-check-the-user-status] this may help you – MimoudiX Mar 02 '19 at 14:24
This however, doesnt stop any user from spamming my database does it? – Mar 02 '19 at 14:32
nothing is unbreakable, but for now this is more secured than the normal sql query – MimoudiX Mar 02 '19 at 14:33

How can I change my SQL statements to be prepared (and secure) statements?

1 Answers1