MYSQL and PHP questions

Forums › IT Pro and developers › MYSQL and PHP questions

martyyn

1971 posts

Uber Geek

ID Verified

#177487 4-Aug-2015 11:15

I've been wanting to learn how to build a basic WordPress site and use PHP to interact with a MYSQL db. Pretty basic stuff and I'm an Oracle Dev so this is just me being curious. I also want the DB interaction to be hidden from the outside world so you have to login to view and run any of it. I've done a few order management type things in the past so that's what I'm basing this on. Add/edit/delete customers and orders. Just really simple stuff to start with.

So far I've done the following:

1. Built a basic Wordpress site, with a couple of normal pages open to everyone and then some password protected pages for the order stuff (more on this later). The order pages have simple forms on them with actions to either insert/edit/delete data from the DB. I've been using a plugin called Shortcode Exec PHP to handle the PHP in the pages as I couldn't get it to work when I created a template.php page from the existing post.php page as described on several WP sites.

When id's need to be passed between pages I've been using $_GET and $_POST as appropriate on the receiving page. I've done various things to ensure the variables are valid including isset, isnumeric, mqsql_real_escape_string and htmlspecialchars, etc.

2. The PHP to handle the actual inserts/updates/deletes I put into physical .php files which I sat in the root directory on the webserver. I just seemed easier to do it that way at the time.

3. Just played around with presentation using CSS and it all looked ok and seemed to work ok.

I knew the password protected pages weren't enough and I noticed they were being returned in Google search results. So I set the pages to private, added noindex/nofollow in WP and requested their removal from Google, which happened pretty fast. I then created a new user, created a new role with access to only private pages and redirected their WP login to the first private page.

I also noticed I could run the php files if I knew the URL and just added any old number for the id. So this is where I'm currently at. What's the best way to ensure these can't be run by anyone who isn't logged into the site ? I've read various options about changing directories and using .htaccess to limit access but I haven't been able to get my head around how that works, especially if I wanted to access this remotely. I was just looking at blocking everything but setting a whitelist for me locally but that didn't seem to make sense.

Or should I just continue with using the plugin and remove them from being physical pages and have them as shortcodes ? Would that stop them from being run from the browser ?

I'd appreciate any pointers.

itxtme

2102 posts

Uber Geek

#1358556 4-Aug-2015 11:46

Not a wordpress developer so cant talk specifically about this.

First off you state you are using mqsql_real_escape_string. Just wanted to check that you are in fact using mysqli or PDO class? The plain mysql interface is set to be discontinued so shouldnt be used on new developments. Shouldnt have been for years, but people still do.

As a web app dev I would suggest using a lockout function at the beginning of each page. Basiaclly there must be a SESSION variable when a user logs into wordpress. Have the lockout.php included on each page you want to check the variable exists. Something as simple as

<?php
if(!isset($_SESSION['logged_in']) || empty($_SESSION['logged_in'])){
//Redirect
header("Location: http://example.com/myOtherPage.php");
die();
}

$_SESSION['logged_in'] is a faux variable you need to work out what the worpress version of this is.

Jeeves

301 posts

Ultimate Geek

#1358741 4-Aug-2015 15:01

Id perhaps think about exactly what it is you want to learn. If it's how to make and modify wordpress websites, then research wordpress and it's own language structure, webhooks, coding style, framework etc.
If you want to learn php/mysql then get rid of wordpress completely and start from scratch.

Wordpress has become such a behemoth these days it's a career in it's own right.

Aside from that, a couple of notes:

Use filter_input functions to verify and sanitize your get and post variables. Eg - if a post variable is a integer

 if(filter_input(INPUT_POST, 'yourVariable', FILTER_VALIDATE_INT)){
       echo "it's a number lol"
} else {
 exit("sod off")
}

If the post data is going straight into a db, don't bother with the above and use prepared statements, PDO or mysqli (not mysql for the love of god).

htmlspecialchars is for when you want to print something from a user (be it a direct post or pulled from a db entry that was created by user entry).

But really, just sit down and google the poos out of everything. stackexchange will become your second home.

martyyn

1971 posts

Uber Geek

ID Verified

#1360537 7-Aug-2015 09:53

Thanks for the replies.

I just wanted to learn how I could use the database installed from within WordPress because I have a number of clients who have WordPress sites and every now and then I think I could provide them with a solution to a 'business process' they currently do manually. I've built a number of front-ends to databases and warehouses, all in Oracle, and so wanted to see if I could transfer some of those skills.

I've just been using the $wpdb class to access the database and it's been relatively easy to set up and get working for basic inserts/update/delete funcionality.

My concern was how secure the PHP code to do the actual insert/update/delete was because I created standalone pages (insert_customer.php, update_customer.php, etc) in the root directory and then found I could run it directly from the browser without having to log in first.

danfaulknor

939 posts

Ultimate Geek

Trusted

Prodigi

#1360619 7-Aug-2015 12:04

If you want to use wordpress as a platform for other things, consider creating them as plugins instead of standalone pages, that way you can use the WP permissions system.

That being said it is possible to use the WP systems in standalone pages, but it's not the intended use.

they/them

Prodigi - Optimised IT Solutions
WebOps/DevOps, Managed IT, Hosting and Internet/WAN.

martyyn

1971 posts

Uber Geek

ID Verified

#1360811 7-Aug-2015 18:08

itxtme:
As a web app dev I would suggest using a lockout function at the beginning of each page. Basiaclly there must be a SESSION variable when a user logs into wordpress. Have the lockout.php included on each page you want to check the variable exists. Something as simple as

<?php
if(!isset($_SESSION['logged_in']) || empty($_SESSION['logged_in'])){
//Redirect
header("Location: http://example.com/myOtherPage.php");
die();
}

$_SESSION['logged_in'] is a faux variable you need to work out what the worpress version of this is.

I came up with something very similar this morning. What I ended up doing was wrapping each php page with

<?php
if (is_user_logged_in()) {
-- get variables
-- validate variables
-- insert/update/delete as necessary
}
} else {
wp_redirect(home_url());
exit;
}
?>

It all works if I'm logged in either as an administrator or the new user I created who only has access to private pages.

I still think I have a mismatch of styles and it could be prettier so I'll work on that next week.

Thanks

martyyn

1971 posts

Uber Geek

ID Verified

#1360812 7-Aug-2015 18:10

danielfaulknor: If you want to use wordpress as a platform for other things, consider creating them as plugins instead of standalone pages, that way you can use the WP permissions system.

That being said it is possible to use the WP systems in standalone pages, but it's not the intended use.

I was reading something yesterday which made me think of this, I think it was suggesting creating my standalone php pages as my own shortcodes and not using the shortcode plugin.

This could well be V2 :)