bcwp

Glad we could help. With regards to output, in the examples you’ve provided so far you should be fine displaying the data exactly as it appears in the database (unless you need to reformat it for some reason).

Generally, you’d concern yourself with validating input before you save it to the database. In some cases a person might try to embed some malicious code (i.e., JavaScript) into a comment or post. This rogue input might pass your SQL injection filter but would be harmful if you output it to the browser of an unsuspecting visitor. So you’d need to come up with a filter or validation check that does more that just look for SQL injection attacks.

However it’s difficult (or impossible) to detect every type of exploit you might encounter. So, in some cases it doesn’t hurt to use some kind of output filtering (like htmlspecialchars or strip_tags) before displaying strings of user-submitted data. But things like dates, numbers, etc. should be fine as-is.

Actually, I may be wrong. It appears $wpdb->insert() and $wpdb->update() have some security mechanisms built in.

However, I still recommend the PHP sanitize and validate functions for data integrity, if nothing else.

Also, the way you were placing the variables directly into your SQL string in your previous question could be susceptible to SQL injection attacks:

"SELECT marriage_id FROM $table_name2 WHERE person_id = id and spouse_id = $spouse_id"

It’s best to do the fine-grained validation and sanitizing yourself rather than relying on any generic string cleanup methods or magic provided by WordPress behind the scenes.

I think sanitize_text_field() already does what your check_input() function is doing (i.e., stripping tags and the like). That said, I don’t think you’re safe from SQL injection attacks using just the methods above.

You might want to check out some examples of common exploits to see the kind of stuff you want to filter out. Wikipedia has a good introduction here:

https://en.wikipedia.org/wiki/SQL_injection

That said, you’re probably thinking “Hey, I value my time. I just want to insert some data into a table, not become an security expert!” If so, then I completely agree.

The simplest way to protect your database is to filter each input specifically to suit type of data you expect. So, if you’re expecting a date in the format of “YYYY-MM-DD”, then it better arrive as “2013-04-05” and not “2013/04/05” or “Hello World”.

But unless I’ve missed something in the documentation, WordPress doesn’t offer anything that specific. So, I like to use the PHP sanitize and filter functions for more fine-grained control over input validation:

https://php.net/manual/en/filter.filters.sanitize.php
https://www.php.net/manual/en/filter.filters.validate.php

If you use those you should be ok, especially with FILTER_VALIDATE_REGEXP.

You’re pretty close.

First, I’d encourage you to use more descriptive variable names rather than generic ones like $table_name and $table_name2. It’s easy to get confused with non-descriptive names. Imagine what would happen if you had five or six tables to join.

I believe c_cav and I were both referring to the “person table” as “$table_name” and the “marriage table” as “table_name2”.

With c_cav’s SQL, the substitution should be as simple as:

SELECT DISTINCT
    a.first_name AS spouse1_first_name,
    a.family_name AS spouse1_family_name,
    j.date_of_marriage,
    j.marriage_id,
    b.first_name AS spouse2_first_name,
    b.family_name AS spouse2_family_name
FROM $table_name2 j
  INNER JOIN $table_name a
    ON a.id = j.person_id OR a.id = j.spouse_id
  INNER JOIN $table_name2 b
    ON b.id = j.person_id OR b.id = j.spouse_id

Notice how only three substitutions for $table_name and $table_name2 were required: one in the FROM clause and two in the JOIN clauses. This is possible because we’re using the table aliases ‘a’, ‘b’, and ‘j’, so you only need to define the alias once, then you can use the aliases everywhere else in the SQL. You can use aliases on tables or columns. The “AS” keyword is optional, but I prefer it for readability.

On that note, I think c_cav arbitrarily chose ‘a’, ‘b’, and ‘j’ for example purposes. It works, but again, the letters ‘a’, ‘b’, and ‘j’ are no more descriptive than “table_name” and “table_name2”. In your actual code, you’d probably want to use something more descriptive like ‘p’ for person and ‘m’ for marriage… or just ‘person’ and ‘marriage’. Whatever is best for readability.

When you want to get the value of spouse 1’s first name, you’ll use the column name “spouse1_first_name”, not “first_name” or “a.first_name”.

Hope that helps.

Awesome. Glad I was able to help. ??

I think you misunderstood me regarding phpMyAdmin. I was suggesting you use it to create an export file and then your plugin would handle the import, not have the end user import it via phpMyAdmin.

As for option 3, you’d have to parse the CSV file in some way using PHP. For that there are many different options. The easiest way appears to be using LOAD DATA INFILE SQL, as suggested above:

https://dev.mysql.com/doc/refman/5.1/en/load-data.html

If that doesn’t work, just read through the CSV file line-by-line and explode() that line/string using ‘,’ comma as your delimiter. The resulting array will contain your column data. So, just do an INSERT …. VALUES $array[0], $array[1],… $array[4] etc. for each row. You can use the PHP file functions to accomplish this.

This might work, as well:
https://php.net/manual/en/function.fgetcsv.php

I don’t do a lot of work with CSV files, so I don’t know the best way to parse one. I’d probably do it line-by-line with fopen() and fgets(), myself, but the following thread might give you the code samples you need to get started:

https://stackoverflow.com/questions/7422292/output-csv-to-array

If I understand you correctly, then I’d first caution you that any solution you come up with should check to ensure the table doesn’t already exist, and if it does, either truncate it before inserting the records or don’t insert them at all. That will prevent errors and/or duplicate records from being created if the user deactivates and then reactivates the plugin.

That said, I think you have three options (someone correct me if I’m wrong):

1) keep the data in a centralized database and have each client/plugin feed off of this centralized data source (i.e., through an AJAX call).

2) Populate the table on your development computer with all the records and then use a tool like phpMyAdmin’s export utility to generate a SQL dump (INSERT statements) of all the records. Then, save the results as a file in your plugin folder. When the plugin activates, simply read the file, extract the SQL statement(s) and execute them.

3) Store the data in some other format, like a CSV file. This option makes sense if your master list is stored in Excel instead of MySQL. When the plugin loads, loop through each line and build/execute the appropriate INSERT statement. Or, you can try the suggestion in the link below regarding “LOAD DATA INFILE SQL”. However, I have never tried that, so I can’t vouch for it… but it sounds interesting.

Of all the solutions above, solution 1 is the optimal choice because it’s the least hassle to maintain. There’s no need to do anything when the plugin activates, and if the data changes or need updates, you only have to do it in one place — on the master server. If your plugin is going to be used on more than one WordPress install, maintaining separate repositories is going to quickly become a headache.

That said, if this is only ever going to be installed on one WordPress site, then you might be ok. Have a look at the following discussion:

https://stackoverflow.com/questions/9734486/importing-a-csv-into-phpmyadmin

or try googling “phpmyadmin import csv file” or “mysql import csv file”. But I’d avoid doing this on the plugin’s activation event, if at all possible. Better to add the records through phpMyAdmin if you only have one install site.

It’s been a long day, so I’m providing the following SQL, untested and off the top of my head, but I think you’re probably looking for something like this:

SELECT
	spouse1_info.first_name AS spouse1_first_name,
	spouse1_info.family_name AS spouse1_family_name,
	marriage_info.date_of_marriage,
	spouse2_info.first_name AS spouse2_first_name,
	spouse2_info.family_name AS spouse2_family_name
FROM table2 AS marriage_info
INNER JOIN table1 AS spouse1_info
ON marriage_info.persion_id = spouse1_info.id
INNER JOIN table1 AS spouse2_info
ON marriage_info.spouse_id = spouse2_info.id

However, if your marriage info table has a record for each spouse, then you’re going to see the relationship show up twice in your results, like
Bob, Jones, 1988-01-02, Sally, Jones
Sally, Jones, 1988-01-02, Bob, Jones

If that’s a problem, then you should consider just storing the marriage_id in table1 for each person and get rid of the person_id and spouse_id fields in table2. In fact, that makes more sense than the method above.

Also, make sure you’re cleaning the values in those variables before using them in your SQL queries. As your code stands right now, you’re quite vulnerable to SQL injection attacks.

Thanks for looking into this. I manage several WordPress websites across different hosts, and I’ve noticed a huge increase of brute force attacks in the last week against all of my sites.

I use the Limit Login Attempts plugin on each of them, and I’ve noticed that some IPs continually circumvent the lockout period.

They’re always targeting the “admin” account, which I never leave as “admin”. I know they’re using proxy servers, and it’s probably one or two people behind 99% the attacks we’re seeing, but we should at least force them to exhaust their allocated IPs.

A cool “opt-in” feature for Limit Login Attempts would be a master log that keeps track of all the locked-out IPs from all of the participating websites. If we could boil it down to a specific set of distinct addresses in a given period of time, we would know which IPs to block, even before they attack. Plus, we’d have a better chance of reporting these guys to the authorities.

Yes, they could always get another block of IP addresses or change proxy providers, but at least they’d have to work harder.

I recommend you use the standard HTML tags for this task, unless you have a really good reason to do differently. So, your main heading would be an H1 tag, and your subheadings could be anything from H2 to H6 tags. Paragraphs should use P tags. You can then style them in your CSS file like so:

h1 { color: red; }
h2 { color: blue; }
p { color: green; }

or, if you want to only style a specific class of these, you could do something like:

h1.heading { color: red; }
h2.subheading { color: blue; }
p.paragraph { color: green; }

To assign the classes to your content, you may have to edit your PHP files in a text editor or use the HTML editing tab in the WordPress content editor… depending on your situation.

I’m not sure how to remove the tags from all your posts, en masse… unless you want to just no display them, altogether… then you could just remove get_the_tags() from your template.

If you only have 10 posts, then tags really aren’t necessary. Tags are really only useful if you have lots of posts, maybe 50+. Tags then create a simple way for people to find related posts across categories.

As for whether it will affect your page results in Google. The answer is no, not negatively, anyway. While tags are links that may contain relevant keywords, which can be good for SEO, it can also be bad for SEO if Google sees this as keyword stuffing. Since your ratio of keywords/links to pages is high, it should have no effect to remove the links because Google is probably already ignoring them.

If you really want to be sure, set up a Google Webmaster account, and they’ll alert you if/when there’s a problem with your content.

And, if in doubt, it’s always best to get the advice of a lawyer before you proceed.

Sorry if my replies sounded angry. I don’t think you’re going to run into any licensing issues unless you’re doing something really unusual in your business model.

I (like thousands of other people) earn a living developing websites in WordPress. There’s absolutely nothing wrong with selling the themes and plugins you create or charging for your time to train users or setup and maintain websites. You could even charge a monthly fee for hosting, backups, security services etc.

The only thing you can’t do is charge money for the WordPress core or the MySQL software. But since they’re available for free online, it would be pretty hard to find a customer who would be foolish enough to pay for them anyway.

There’s nothing stopping you from building a theme or some plugins and charging your customer $5000 or whatever for the software. Heck, if you can find a customer who’s willing to pay $1 million for your themes, then you’re certainly allowed to do so. Charge for whatever you like as long as the WordPress/MySQL files are included for free… or not included at all.

The main point is that WordPress, MySQL, and any third-party plugins remain the intellectual property of the people who create them. That’s pretty much it.

Because you’re the author of your themes and plugins, it’s 100% your choice whether you sell your customer ownership of your code or if you only license it to them for one specific project.

Does that clear up your concerns?

BTW, you might want to consider all the unnecessary fires you’ll be putting out in the future…

If all you’re asking is: Is it possible? Then the answer is yes. If you’re familiar with PHP and RDBMSs, then it’s not even that difficult. In fact, I’m sure I could do it in one afternoon. I’m also sure that it would come back to haunt me in the future.

An experienced programmer would see that eventually someone is going to install a plugin, or there’s going to be a WordPress update or some other change and BAM! the system is going to break. That’s precisely why people constantly advocate against modifying the core of WordPress, and like it or not, at the moment MySQL is part of that core.

I don’t really care whether you like my answer or not. You failed to answer the question: What exactly are you doing that would violate the license agreement? There are millions of companies using WordPress for commercial purposes. What makes you so special?

In fact, you didn’t even explain what exactly you need help with! All you said was:

“It’d be so appreciated if you could help me some about this…”

You answered your own question with the links you provided in your first post.

If you’re trying to modify WordPress to the point that you can resell it as your “own” CMS, then you might as well just write your own CMS from scratch (like many companies choose to do) or find another CMS that suits your needs.

For non-core functions, accessing another database server or creating some custom tables is reasonable, but moving the WordPress core tables from MySQL to, say PostgreSQL, is about as stupid as trying to rewrite the core to use ASP.Net instead of PHP… so you’re unlikely to get the answer you’re looking for from the WordPress community because not many people are going to waste their time on such a task.