Should this code be changed or updated?
Somehow, through my users registration page, there are fake users registrering and populating the database table.
I thought that I had my code set up so that I would not get an SQL attack? Following is my code.
if ($u && $e && $p && $fn && $mi && $ln) { // If everything's OK...
$q = "SELECT id FROM users WHERE email=?";
$stmt = mysqli_prepare($db, $q) or trigger_error("Query: $q\n<br>MySQL Error: " . mysqli_error($db));
mysqli_stmt_bind_param($stmt, "s", $e);
mysqli_stmt_execute($stmt) or trigger_error("Query: $q\n<br>MySQL PS Error: " . mysqli_stmt_error($stmt));
$r = mysqli_stmt_get_result($stmt);
if (mysqli_num_rows($r) == 0) {
// Available.
// Create the activation code:
$a = md5(uniqid(rand(), true));
// Add the user to the database:
// Make the query:
$q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, active, agree, date_expires) VALUES (?, ?, ?, ?, ?, ?, ?, 'Agree', DATE_ADD(NOW(), INTERVAL 2 YEAR) )";
// Prepare the statement:
$stmt = mysqli_prepare($db, $q);
// Bind the variables:
mysqli_stmt_bind_param($stmt, 'sssssss', $u, $e, $p, $fn, $mi, $ln, $a);
// Assign the values to variables:
$u = $_POST['username'];
$e = $_POST['email'];
$fn = $_POST['first_name'];
$mi = $_POST['mid_initial'];
$ln = $_POST['last_name'];
$a = md5(uniqid(rand(), true));
// Execute the query:
mysqli_stmt_execute($stmt); ASKER
SO how would this be happening IF my code IS stopping an sql injection.
I think you might be misunderstanding the concepts here.
Let me try an analogy to help explain.
Let's say you're filling out a government form, like an application to get a driver's license. You get the application filled out and you get someone official to put in their signature so it's ready to be submitted.
And now let's say that you use a pen to carefully alter the form so that instead of applying for a driver's license, you completely change the intention of the form so that it becomes an authorization for some government check payment, and you leave behind the official signature.
That is the equivalent of SQL injection - carefully manipulating something that should do A to do B instead.
In your case, you're seeing a lot of fake registrations. That isn't SQL injection, that's just spam.
It's the same as you filling out 100 applications for a driver's license. You're not manipulating anything - you're just filling out a bunch of them and using fake info to do it.
By using prepared statements, you've successfully prevented SQL injection but that doesn't stop spam submissions.
By using email verification, you've successfully stopped bots / spammers from fully activating their fake accounts but that doesn't stop them from spamming the very first step where it inserts a record into the database for the user.
If you want to stop the spam from that first step, you just need to use some kind of captcha mechanism. There are many existing plugins that will do this for you.
Create a pending_users table. Insert into that and let the users know they have X hours to activate or they will need to re-register.
When the go to activate, look form them in pending_users. If found, move the data over to the users table. If not found, make them re-apply.
Create a job to delete everything older than X hours from pending_users.
Won't stop SPAM but will help mitigate it and it will keep junk out of your users table.
To add to gr8gonzo's comment on SPAM:
Nothing keeps me from filling out the form 100 times and clicking submit. There are many programs that let people automate form completion so I wouldn't even have to re-type everything.
And you have duplicate code.
Line 13: $a = md5(uniqid(rand(), true));
Line 31: $a = md5(uniqid(rand(), true));
ASKER
I am working on a Macbook Air.
I don't think I can create a job. I asked my web hosting people if I could do this a few years ago and they seemed to indicate that I couldn't create a job. Unless maybe I was not using the proper terminology at the time. I don't have my own server.
Understanding the situation more clearly now. I get very little support from my hosting company.
Quick google seems to imply MySQL does as well. Now, if your hosting company allows you access to it, I have no idea:
https://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/
Bear in mind that the activation code is simply a secondary line of defense. Implementing a captcha should reduce almost all of the spam sign-ups.
Your code only stops sql injection not fake email addresses.. you need to send an email to the email address given with a link that the user will have to click to validate that the email is valid. Now you can add the user to your database.