DCL SQL GRANT and REVOKE
Posted by mcgelligot in SQL on January 21, 2012
SQL is broken up into several sub-languages. One of these is DCL which stands for Data Control Language. What it is really about is designating who has permission to do what on a database. Ultimately, this is a security thing. If we could implicitly trust everyone, we would just grant all power to everyone. In the real world of the anonymous internet this would fall into the category of FAIL.
Thus, SQL gives us the ability to GRANT and REVOKE. Actually, on php myadmin in cpannel, you cannot do this with an SQL command, it is done through a UI when the user is created. Basically GRANT gives permission to do various things, and REVOKE takes those permissions away.
The system works logically in that a user has no permissions unless first granted them. Thus we have no REVOKEs without first, somewhere along the line, having a GRANT. The key to GRANTs is to keep them to a minimum. The less access users have to a database the less likely they are to screw things up. Of course, this must be balanced with the needs of the many, which Spock will tell you, out-weighs the needs of the few, we happy few, we band of brothers. Never thought you would see Spock and Henry V quoted in the same sentence did you? Suffice it to say there is a balance.
For most purposes, REVOKEs are used when an empolyee leaves a company or a user loses status (say a subscription lapses). The syntax for REVOKE follows:
REVOKE privilege
ON object
FROM user or PUBLIC or role
For a GRANT, just replace “REVOKE”.
SQL Nullification: IFNULL(), ISNULL(), NVL(), COALESCE()
Posted by mcgelligot in SQL on January 18, 2012
Okay, let’s get our act together. To find out if a cell is NULL why not just ask…ISNULL? Well, yes, this does work for mySQL. But it isn’t that simple. What if we wanted an easy way to replace the null value with something else? Good idea you say. So the powers that be at mySQL came up with IFNULL(). It looks like this:
SELECT IFNULL(column1, “alternative”) FROM table
So if the expression in column1 turns out to be NULL you can make it something else.
Where it gets a bit confusing is that some applications use ISNULL() the way mySQL uses IFNULL(). And some use a completely different syntax. NVL() is how it looks in Oracle.
But there is another way of doing this available in most applications. It is called COALESCE(). This looks alot like IFNULL (mySQL) but has the added feature that it can take multiple parameters and will in the end return the first one that it finds that is NOT NULL. So you can do something like this:
SELECT COALESCE(column1, column2, etc., “alternative”) FROM table
This will return the first not null param that it runs across. Leave the failsafe for the end.
All About SQL JOINs
Posted by mcgelligot in SQL on January 12, 2012
Normalization is a way to reduce the amount of time it takes for tables to do their processing. It involves reducing tables to the least number of necessary columns. However, this can create a problem when putting the data back together to present desired output. To solve this problem, JOINs were invented in SQL. In a JOIN two columns from two tables are compared to find out if rows should be meshed together, then the rows are combined. It effectively makes one table an extension of another.
For example, we have a list of customers and a list of transactions. We have a customer number for each customer and that customer number is listed in the transaction log. Since we already have all the info we need about our customers in the customer table, we do not need to also store it in the transaction log. All we need do is list the customer number. So when we need to send an invoice to a customer, we can perform a JOIN on the two tables. This will recall the transaction and with the reference to the customer table get the name, address, etc. Alternatively we could also use a JOIN to find all of the transactions by any specific customer.
For the NEWBIE there is a bewildering list of SQL JOINs. Yet they all have a purpose:
First there is the INNER JOIN. This combines the information from two tables based on a comparison of two columns. It outputs only rows that are found in both tables.
A LEFT JOIN will output all the rows in the left table (or first) table and any associated info from the right table. If there is no info from the right, it will leave those cells blank.
The RIGHT JOIN is similar to the left, only in this case, it is all the rows on the RIGHT that will be listed with unfilled cells left blank.
A FULL JOIN is like a RIGHT and LEFT JOIN together. All rows are listed with the appropriate rows meshed. Blanks will be left in rows with unmeshed fields.
To confuse matters perhaps slightly, there is also the UNION. This works alot like a JOIN only it combines two SELECT statements rather than tables. The result is a list of all the unique info from two selections from like table columns.
Hope that clears things up.
PHP mail() function and From Header
Posted by mcgelligot in Php Coding on January 5, 2012
Yes, I knew you wanted to read about another development on the PHP mail function front. So here it is: you can use the “from” header on some servers and not on others. Some mailservers will reject an email with a “from” header (with out of state plates) out of hand, on the possibility it is spam. Others will allow you to send a message with it.
Why do I need to include the “From” header in a mail message? As previously explained, I am developing a universal contact module that can be included on any website. Not all that complicated…you would think. But as my dear old granddad used to say between spits of tobacco, “It’s always sumpin’.”
I wanted to make it easy to respond to messages sent by the contact form, so I included the “From” and “reply to” headers. It worked fine on one server, and I thought, great, now I will just start installing this module on all my sites. The first one liked the “From” module, so did the second, but when I hit a different server, it did not like it.
As it turns out, you don’t really need the ”From” header in the email to allow a one click response form to show up on your email program. So, I have simply removed it. The “reply to” still works fine.
PHP Mail Function and the New Line Character
Posted by mcgelligot in Php Coding on January 2, 2012
Lately I have come to the notion that I can modularize my PHP and inject it like a serum into some of my older websites to update and upgrade their performance and useability. In pursuit of this goal, I rewrote a mail utility to make it universal, a kind of plugin. Simply FTP some files to the server, install a link and wham.
I put the utility on Drywall How To Manual. It works great…now. There was only one tiny problem. When I rewrote the utility, I changed the $message variable to look something like this:
$message = “Name: $cname \n Email: $cemail \n Ref Page: $ref_url \n Message: $cmessage \n”;
It feeds the following:
$mail_success = mail($email, $subject, $message);
Do you see anything wrong with it? Neither did I. It took me an hour of testing and breaking down those lines of code to find out what the problem was. I will give you a hint if you haven’t already got an inkling from the title of this post. The problem is in the new line character.
Yes, you can have a new line character in the variable of the mail function in PHP. Only thing is you cannot have it at the end JUST BEFORE the quote mark.
The funny thing is that this does not throw an error. In fact, the email is simply not sent, but the function returns “true”, indicating the message was sent.
To test to make sure that the “backslant n” was not a general no no in PHP I tried a little test which looked like this:
//test in variable:
$test = “test \n”;
$test2 = “test2 \n”;
print “test = $test”;
print “test2 = $test2″;
print “test3″;
print “\n”;//test in function:
$length = strlen($test);
print “length = $length”;
The results make me believe this is a little bug in the mail() function:
test = test
test2 = test2
test3
length = 6
Interesting development. No matter how long you are in the game, you always are learning something.
PHP isset() and Form Input
Posted by mcgelligot in Php Coding on December 27, 2011
It is easy to fall prey to simple errors when writing PHP. Usually when a script spits out an error it is because there is no semicolon or a quote mark is in the wrong spot. Even so, PHP is fairly forgiving and also explicit in warning of mistakes, but sometimes simple errors can also be deceptively hard to find.
The isset function is frequently used in if statements to test for a condition. Yet it can also fool the programmer when dealing with form input. For example input from a form looks something like this:
<input type=”text” name=”book” value=”<?php print $_SESSION[book]; >” />
However, the $_SESSION[book] variable happens to be empty, because say a book has not yet been chosen. What is more when the user submits the form, he does not fill in the “book” data.
Now if the script receiving this data looks like this:
if(isset($_SESSION[book]))
{
$x = “Eat chopped liver.”
}
Then someone is going to be eating chopped liver because even though there is nothing in the variable $_SESSION[book], the variable is still SET.
SSI: Include Virtual in Subdomain
Posted by mcgelligot in Domains, Php Coding on December 26, 2011
SHTML is not the rage that it once was. It was a huge advancement over HTML because it could be used to more easilly control the formatting of a website. I personally created quite a few websites using the technology.
Now, when I am converting some of those sites to use PHP and access a data base, I find an interesting thing happens when attempting to include a file from a directory within the main domain. Of course, different servers will set this up differently, but usually the subdomain is relative to the main domain in the same way a directory would be. You would think, then, that you could use an include in a file in a subdomain to directly access a file higher up the directory tree in the same domain, but not within the subdomain. Well, you can’t….unless you don’t do it directly.
A work-around to this is create a .php file in the subdomain that includes the file up the directory tree that you want included in the .shtml file and access that file using a virtual include:
<!–#include virtual=”include.php” –>
You may think that you could just use your .htaccess file to make .shtml parse like PHP. This did not work in my server, perhaps because I wanted to continue to use some of the SHTML capabilities.
You may wonder why you would even want to do this. Why not just change the name of the files to .php or even create a perma page that is handles through the .htaccess? The problem here is that you lose some of the benefit of good standing in the search engines. So, with a bit of a hack, you can keep your .shtml file extension and its capabilities in a subdomain while accessing files within the domain.
Useful Links:
