Post by avsrthe » Mon May 22, 2023 8:55 pm

Hi
I am using Opencart 3.0.3.8
Basically I created a code in php to block customers from account registration if email and telephone, first name and address is in blacklist. For that in the catalog/controller/account/register.php I added array that holds list of blacklists as below:
$blacklistedemail = array("email1@gmail.com", "email2@gmail.com","example@example.com");
$blacklistedtelephone = array("9999999999", "5555555555","2222222222");
$blacklistedfirstname = array("Raj", "ken", "ber");
$blacklistedaddress = array("street1", "street2","street3");

with the rest of codes in php, validation works and it blocks customers in blacklist. But, using arrays like the above is not standard and it is Ok for testing validation code and small data. But using database will be good choice .
Instead of using php array list , I needed to change it into database query and result. For that I created a table called " oc_blacklist". In this table there are four columns 'email' , 'telephone', 'address', 'firstname' which contains blacklist. I tried some sql queries but it breaks and registration page becomes blank or give internal server error 500.
How to query DB and get email, telephone, address and firstname blacklist in arrays set in respective variables, $blacklistedemail, $blacklistedtelephone, $blacklistedaddress and $blacklistedfirstname. Please help me.
Thank you
AVSRThe
Last edited by avsrthe on Thu Jun 01, 2023 7:52 pm, edited 1 time in total.

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by ADD Creative » Tue May 23, 2023 12:18 am

Check you PHP or server error logs. It should tell you what the 500 error is.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by straightlight » Tue May 23, 2023 1:25 am

Also, post the model that involves your project.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by softmonke » Tue May 23, 2023 4:53 am

You can try adding the following to your "index.php" file and it should show you the errors when you access the page/submit the form:

Code: Select all

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

Reach out to us at hello@softmonke.com for your OpenCart web development needs.


User avatar
Active Member

Posts

Joined
Tue May 23, 2023 4:42 am


Post by avsrthe » Tue May 23, 2023 6:11 am

@straightlight
There is no model or any other module in this. I add blacklist data manually using phpmyadmin.
The php in_array see the blacklist and throw error if user submits the form by the code:

If I use php array for ex. : $blacklistedemail = ["email1@gmail.com", "example2example.com" , "someone@somemail.com"]; , everything works.
But I wanted to deleted this php line and substitute with DB query and assign the result to the variable $blacklistedemailI. I don't know to query database and put that result in the variables like. $blacklistedemail.
Basically I tried for ex.

$query = $this->db->query("SELECT 'email 'FROM 'oc_blacklist' ");
and similar codes, but not perfect, as I don't have good knowledge in the syntax to be used in Opencart. Also stuck up on how to fetch all rows containing email addresses in the column. I need to Query and fetch results having all email addresses in the column 'email' (all in the rows) from the table 'oc_blacklist ' and assign the variable $blacklistedemail to it. Please help me with right syntax for one variable $blacklistedemail, I will follow it and do for the rest of columns like 'telephone' 'firstname' etc....
Thank you
Avsrthe
Last edited by avsrthe on Tue May 23, 2023 1:17 pm, edited 4 times in total.

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by straightlight » Tue May 23, 2023 7:46 am

While what you have posted are partial codes, OC does not use $post but rather:

Code: Select all

$this->request->post
However, since you are requiring assistance with custom codes, you could always create a new service request in the Commercial Support section of the forum or contact me directly via the forum PM to get this done as a custom job.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by avsrthe » Tue May 23, 2023 8:13 am

straightlight wrote:
Tue May 23, 2023 7:46 am
While what you have posted are partial codes, OC does not use $post but rather:

Code: Select all

$this->request->post
However, since you are requiring assistance with custom codes, you could always create a new service request in the Commercial Support section of the forum or contact me directly via the forum PM to get this done as a custom job.
Hi
I had already assigned $post['email'] = $this->request->post['email'];
this works in php and throws error and blocks when a blacklisted user tries to register. I am not asking for full custom work.
I am asking help to query DB and syntax for this particular variable. Just substitute php array method line to DB query result.
If you can help me with Sql syntax, it will be helpful..
Thank you

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by avsrthe » Tue May 23, 2023 1:27 pm

@ADD Creative and @Straighlight
I have figured it out and found which seems to be correct syntax for the DB query with two lines of code as below: I removed table prefix. Table name is 'blacklist' and the column is 'email'. In this column blacklisted email addresses are manually added in phpmyadmin.
$query = $this->db->query("SELECT email FROM blacklist ");
$blacklistedemail = $query->row;
At present the above queries 1st row of the column. Do we need to use foreach or something ? I tried, but page breaks because I dont know the syntax correctly.. It is done almost nearest to what is needed. Only thing is that all rows values must be retrieved and used in a variable to validate.
Please help me to fetch all rows from the the above query into $blacklistedemail. Then this $blacklistedemail variable will be used for validation. Please help.

Thank you for your replies
AVSRThe

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by softmonke » Tue May 23, 2023 5:32 pm

avsrthe wrote:
Tue May 23, 2023 1:27 pm
@ADD Creative and @Straighlight
I have figured it out and found which seems to be correct syntax for the DB query with two lines of code as below: I removed table prefix. Table name is 'blacklist' and the column is 'email'. In this column blacklisted email addresses are manually added in phpmyadmin.
$query = $this->db->query("SELECT email FROM blacklist ");
$blacklistedemail = $query->row;
At present the above queries 1st row of the column. Do we need to use foreach or something ? I tried, but page breaks because I dont know the syntax correctly.. It is done almost nearest to what is needed. Only thing is that all rows values must be retrieved and used in a variable to validate.
Please help me to fetch all rows from the the above query into $blacklistedemail. Then this $blacklistedemail variable will be used for validation. Please help.

Thank you for your replies
AVSRThe
That's the correct way to query from your database in OpenCart. However, $query->row will only return the first result from your query.

Code: Select all

$this->db->query
will return an object in the following format:

Code: Select all

{
    "num_rows"	=> number of rows returned from your query, 0 if none,
    "row"	=> first result returned from your query as an array or an empty array if no results are returned,
    "rows"	=> all rows returned from your query as an array of arrays or an empty array if no results are returned,
}
In your case, $query->row will return:

Code: Select all

array (
  'email' => 'email1@gmail.com',
)
and $query->rows will return:

Code: Select all

array (
    0 => array (
    	'email' => 'email1@gmail.com'
    ),
    1 => array(
        'email' => 'email2@gmail.com'
    ),
    2 => array(
        'email' => 'email3@gmail.com'
    )
)
To get each individual email from your query, you can do a simple for-loop like the following:

Code: Select all

foreach ($query->rows as $row) {
    echo $row['email'];
}
Therefore, you can probably do the following to check if the user's email is blacklisted:

Code: Select all

$query = $this->db->query("SELECT email FROM blacklist");
// trim and convert users' input to lowercase to be safe
$trimmed_email = trim(utf8_strtolower($this->request->post['email']));

foreach ($query->rows as $row) {
    if ($row['email'] === $trimmed_email) {
        // there's a match, do something
        break;
    }
}
BUT I would suggest you to simply do a SELECT query with a WHERE clause, like the following:

Code: Select all

$blacklist_query = $this->db->query("SELECT * FROM blacklist WHERE email = '" . $this->db->escape(utf8_strtolower($this->request->post['email'])) . "'");

if ($blacklist_query->num_rows) {
    // there's a match, do something
}
Last edited by softmonke on Tue May 23, 2023 5:46 pm, edited 1 time in total.

Reach out to us at hello@softmonke.com for your OpenCart web development needs.


User avatar
Active Member

Posts

Joined
Tue May 23, 2023 4:42 am


Post by ADD Creative » Tue May 23, 2023 5:40 pm

You would need num_rows.

Code: Select all

if ($query->num_rows) {
	foreach ($query->rows as $result) {
		$blacklistedemail[] = $result['email'];
	}
}
It will probably be more efficient to use a WHERE clause in you SQL to check if the email in the database.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by avsrthe » Tue May 23, 2023 7:11 pm

ADD Creative wrote:
Tue May 23, 2023 5:40 pm
You would need num_rows.

Code: Select all

if ($query->num_rows) {
	foreach ($query->rows as $result) {
		$blacklistedemail[] = $result['email'];
	}
}
It will probably be more efficient to use a WHERE clause in you SQL to check if the email in the database.
Thank you very much. It works very well. You have made my day good. I tried to add WHERE clause initially, but unsure what to write next to it. The page broke or gives Undefined variable error. For example I tried: $query= $this->db->query("SELECT email FROM blacklist WHERE email = $email"); I know the variable is not define like this. It is wrong.
My table Name is "blacklist" and column is 'email' . email column contains all email addresses as blacklist. Can you please tell what should we add next to WHERE to make this more efficient? If I needed to add something in mytable ? If you suggest, I will do the same. Please help me further to fine tune this. Thank you

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by ADD Creative » Tue May 23, 2023 7:22 pm

The quoting and escaping is very important.

Code: Select all

$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by avsrthe » Tue May 23, 2023 7:37 pm

ADD Creative wrote:
Tue May 23, 2023 7:22 pm
The quoting and escaping is very important.

Code: Select all

$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
Hi
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by SohBH » Tue May 23, 2023 9:20 pm

catalog/model/account/customer.php

Code: Select all

public function getBlacklistCustomerByEmail(string $email): array {
	$query = $this->db->query("SELECT `email` FROM `blacklist` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
	return $query->row;
}

catalog/controller/account/register.php

Code: Select all

$blacklistemails = $this->model_account_customer->getBlacklistCustomerByEmail($this->request->post['email']);
foreach ($blacklistmails as $blacklistemail) {
	$blacklistedemail[] = $blacklistemail['email'];
}

        if (in_array($this->request->post['email'], $blacklistedemail) || (oc_strlen($this->request->post['email']) > 96) || !filter_var($this->request->post['email'], FILTER_VALIDATE_EMAIL)) {
            $this->error['email'] = $this->language->get('error_email');
        }

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by avsrthe » Tue May 23, 2023 10:35 pm

@SohBH
Thank you for posting your code. I tried, but it is giving 500 error. Error log says: unexpected variable "$blacklistemails", expecting "function" or "const" in ......

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by ADD Creative » Tue May 23, 2023 11:45 pm

avsrthe wrote:
Tue May 23, 2023 7:37 pm
ADD Creative wrote:
Tue May 23, 2023 7:22 pm
The quoting and escaping is very important.

Code: Select all

$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
Hi
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
It's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by avsrthe » Wed May 24, 2023 8:35 am

ADD Creative wrote:
Tue May 23, 2023 11:45 pm
avsrthe wrote:
Tue May 23, 2023 7:37 pm
ADD Creative wrote:
Tue May 23, 2023 7:22 pm
The quoting and escaping is very important.

Code: Select all

$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
Hi
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
It's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.
Hi
It is a great help you did.
I think WHERE is very efficient as you said, when Sql query will target particular data of a particular row each and validate.
Because I am validation Firstname and Street address also. If Firstname and Street address together matches, it will block checkout. Now what happens is that it blocks if a name in one row and a different street address in another row matches. It must check name and street address together in a single row. Genuine customers should not be blocked
Shall I I insert "ID" column in phpmyadmin. Then query ID from database wiith name posted and ID from address posted. If both IDs match then block with error messsge. This logic, will it work? Please help me with ID target in the Sql query you mentioned: $query= $this->db->query("SELECT firstname FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
$query2= $this->db->query("SELECT address FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
something like this ? Or any better method ? But ID will not get posted from frontend checkout obviously. But how to target both firstname and particular Street to match in blacklist table?
But as of now your answer with the code works very well for email address, without WHERE clause at present, because email address is already unique.But First name can be same for many people. A better solution would be really helpful. Please help.
Thank you
AVSRThe

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by softmonke » Wed May 24, 2023 11:18 am

avsrthe wrote:
Wed May 24, 2023 8:35 am
ADD Creative wrote:
Tue May 23, 2023 11:45 pm
avsrthe wrote:
Tue May 23, 2023 7:37 pm

Hi
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
It's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.
Hi
It is a great help you did.
I think WHERE is very efficient as you said, when Sql query will target particular data of a particular row each and validate.
Because I am validation Firstname and Street address also. If Firstname and Street address together matches, it will block checkout. Now what happens is that it blocks if a name in one row and a different street address in another row matches. It must check name and street address together in a single row. Genuine customers should not be blocked
Shall I I insert "ID" column in phpmyadmin. Then query ID from database wiith name posted and ID from address posted. If both IDs match then block with error messsge. This logic, will it work? Please help me with ID target in the Sql query you mentioned: $query= $this->db->query("SELECT firstname FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
$query2= $this->db->query("SELECT address FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
something like this ? Or any better method ? But ID will not get posted from frontend checkout obviously. But how to target both firstname and particular Street to match in blacklist table?
But as of now your answer with the code works very well for email address, without WHERE clause at present, because email address is already unique.But First name can be same for many people. A better solution would be really helpful. Please help.
Thank you
AVSRThe
I would advise against trying to blacklist based on first name and address since you will have to blacklist all possible combinations and variations of first names and addresses - since there are no fixed format for first names and addresses. Furthermore, it is too easy for the blacklisted customers to bypass your blacklisting logic.

Reach out to us at hello@softmonke.com for your OpenCart web development needs.


User avatar
Active Member

Posts

Joined
Tue May 23, 2023 4:42 am


Post by avsrthe » Wed May 24, 2023 12:36 pm

@softmonke
Hi
You are right. It is difficult to do all combination of logic and that one can By pass it. I thought to try based on IDs, if possible to target particular name and address . Thank you for your suggestion.
AVSRThe

New member

Posts

Joined
Tue Jan 05, 2021 10:04 am

Post by SohBH » Wed May 24, 2023 4:02 pm

catalog/model/account/customer.php

Code: Select all

public function getBlacklistCustomerByEmail(string $email): array {
	$query = $this->db->query("SELECT `email` FROM `blacklist` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
	return $query->row;
}

catalog/controller/account/register.php

Code: Select all

$blacklistedemail = $this->model_account_customer->getBlacklistCustomerByEmail($this->request->post['email']);

        if (in_array($this->request->post['email'], $blacklistedemail) || (oc_strlen($this->request->post['email']) > 96) || !filter_var($this->request->post['email'], FILTER_VALIDATE_EMAIL)) {
            $this->error['email'] = $this->language->get('error_email');
        }

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia
Who is online

Users browsing this forum: No registered users and 396 guests