Datasbase value check and action if it does, PHP

Question

0.00/5 (No votes)

See more:

Hello all,

I have an question, I have PHP code to create a unique ID but I want to be sure it is unique. If not it has to do an action: create a new ID that is not exists already.
This is my code:

<?php

    function random_strings($length_of_string)
    {

        $str_result = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';

        return substr(str_shuffle($str_result), 0, $length_of_string);
    }

    ob_start();

    echo random_strings(10);

    $Output = ob_get_contents();

    ob_end_clean();



    $conn = mysqli_connect("localhost", "probleem_uniqid", "Password", "DBname");

    if($conn === false){
        die("ERROR: Could not connect. " . mysqli_connect_error());
    }

    $Name = $_POST['Name'];
    $Email = $_POST['Email'];
    $Phone = $_POST['Phone'];
    $Problem = $_POST['Problem'];

    if ($stmt = mysqli_prepare($conn, "INSERT INTO ID (Id, Name, Email, Phone, Problem) VALUES (?, ?, ?, ?, ?)")) {
        mysqli_stmt_bind_param($stmt, "sssss", $Output, $Name, $Email, $Phone, $Problem);
        if (mysqli_stmt_execute($stmt)) {
          echo ' ';
          echo "<div id=\"PHP-output\"> Succesvol Verstuurd </div>";
        } else{
            echo ' ';
            echo "<div id=\"PHP-output\"> Sorry, Het kon niet worden verstuurd, probeer het later nog eens of neem contact op met de klantenservice. $sql.  </div>" . mysqli_error($conn);
        }
    }

    mysqli_close($conn);

?>

What I have tried:

I don't know very much about PHP, but I tried to follow yt tutorials or web tutorials but I can't come out to it.

Posted 22-May-20 0:56am

Sam Vorst

Updated 22-May-20 2:06am

Richard Deeming

v4

Add a Solution

Comments

Richard Deeming 22-May-20 7:01am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

PHP: SQL Injection - Manual[^]
PHP: Prepared statements and stored procedures - Manual[^]

Sam Vorst 22-May-20 7:08am

How do you do that?
Sorry for my unexperience...

Richard Deeming 22-May-20 7:15am

There are lots of examples in the documentation:
PHP: mysqli::prepare[^]
PHP: mysqli_stmt::bind_param[^]

For example:

if ($stmt = mysqli_prepare($conn, "INSERT INTO ID (Id, Name, Email, Phone, Problem) VALUES (?, ?, ?, ?, ?)")) {
    mysqli_stmt_bind_param($stmt, "sssss", $Output, $Name, $Email, $Phone, $Problem);
    if (mysqli_stmt_execute($stmt)) {
        ...
    }
}

Sam Vorst 22-May-20 7:18am

What happens if I don't do this? Is it unsecure or so?

Richard Deeming 22-May-20 7:26am

Extremely insecure.

A hacker could easily read and alter any data in your database, bypassing any permission checks you might have in your code.

They could append a "crytominer" script to the bottom of every record, so that every time your users load a page the script uses their computer to mine bitcoin and send it to the attacker.

Or they could inject a script to redirect your users to a phishing page, or a porn site, or anything else they wanted.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]

Sam Vorst 22-May-20 7:28am

Thanks, but how to apply this in my code, or was the code you send that already?

Richard Deeming 22-May-20 7:31am

That was the code I sent already - use mysqli_prepare, mysqli_stmt_bind_param and mysqli_stmt_execute to pass the parameter values separately from the query.

Sam Vorst 22-May-20 7:45am

Is it good now? Check my solution.

Richard Deeming 22-May-20 7:46am

Looks OK, but you shouldn't have posted that as a solution (unless it has actually solved your problem).

Click the green "Improve question" link and update your question instead.

Sam Vorst 22-May-20 7:47am

Oh sorry, I'm new, thanks for the tip

Sam Vorst 22-May-20 7:50am

Do you know the answer to my original question? Anyway thanks for all help until now!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-05-22T02:07:00

Quote:

PHP

ob_start();
echo random_strings(10);
$Output = ob_get_contents();
ob_end_clean();

As far as I can see, that's a very convoluted way of saying:

PHP

$Output = random_strings(10);

You'll need a SQL query to check whether the ID already exists. You'll need to generate an ID and test whether it exists inside a loop, until you find one that doesn't exist. For example:

PHP

function generate_id($conn) {
    $stmt = mysqli_prepare($conn, "SELECT Count(1) FROM ID WHERE Id = ?");
    mysqli_stmt_bind_param($stmt, "s", $Id);
    mysqli_stmt_bind_result($stmt, $Count);
    
    do
    {
        $Id = random_strings(10);
        mysqli_stmt_execute($stmt);
    } while ($Count > 0)
    
    return $Id;
}

PHP: do-while - Manual[^]
PHP: mysqli_stmt::bind_result - Manual[^]

Once you've opened your connection, call this function to generate a new random ID:

PHP

$conn = mysqli_connect("localhost", "probleem_uniqid", "Password", "DBname");

if($conn === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

$Output = generate_id($conn);
...

NB: There is still a fairly small possibility that two requests executing at the same time might generate the same ID which isn't already in the database, and then try to insert a record with that ID. Assuming your database field is correctly constrained to be unique, one request will fail.