Fix openssl Error

While developing lately in Ruby on Rails I ran into a dastardly error OpenSSL::SSL::SSLError: SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed Which made me go, What the Heck?! After a lot of googling, I found the best way to fix this problem. The best way to fix the OpenSSL::SSL::SSLError: SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed problem is to run just two simple commands.

curl -s http://curl.haxx.se/ca/cacert.pem > ~/.cacert.pem
echo 'export SSL_CERT_FILE="$HOME/.cacert.pem"' >> ~/.bash_profile

I seriously spent a LOT of time looking into this error, and hope this can help some one else fix their error a little faster than I was able to! Feel free to comment suggestions below!

If you liked this post, you can share it with your followers or follow me on Twitter!

MySQL Permissions Tips

Well the last time I posted it was all about MySQL, and I didn't post all I wanted to about MySQL. I've recently learned a fair amount about MySQL permissions which are very handy indeed. Today, I'll walk you through how to set up some basic permissions for a payroll table.

First this is the payroll table that we are going to use:

First step for creating MySQL permissions, is to create a table. We will first need to create a MySQL database to hold our payroll table. To do that all we need to do is type a command inside of MySQL just like this:

DROP DATABASE IF EXISTS RedFlameCompany;
CREATE DATABASE RedFlameCompany;
use RedFlameCompany;

The first line will go through and check if the local MySQL has a database named RedFlameCompany and if it does it drops it. Which means that it will get rid of the MySQL database. The second line means that it creates the database from scratch. The third line then tells MySQL to use the RedFlameCompany database.

Next we will want to create the payroll table so we can give MySQL permissions to see this table:

DROP TABLE IF EXISTS payroll;
CREATE TABLE payroll (
 Dept int(3),
 Manager boolean,
 Name varchar(30),
 Birthdate date,
 Salary int(7)
);

This will also drop the MySQL table if it already exists, then create the MySQL table. Each line is a different column from the original payroll table that we have above.

Next we will actually want to populate the MySQL table with the data from the picture. this is done as easy as:

INSERT into payroll (Dept, Manager, Name, Birthdate, Salary) VALUES
 (1, true, "BOB", "0001-01-01", 50000),
 (1, false, "BILL", "0002-02-02", 40000),
 (1, false, "BART", "0003-03-03", 70000),
 (2, true, "JIM", "0004-04-04", 40000),
 (2, false, "JANET", "0005-05-05", 50000),
 (2, false, "JACK", "0006-06-06", 60000),
 (3, true, "MARY", "0007-07-07", 50000),
 (3, false, "MARTHA", "0008-08-08", 70000),
 (3, false, "MARTY", "0009-09-09", 90000);

Make sure to use the column names that you recently created, and then put your values in in the same order that you listed them as seen above.

Next we will want to create some MySQL views. Views are an easy way to grant MySQL permissions to someone whom you don't want be able to see all the MySQL database, and someone you especially don't want to have the ability to update or insert on your MySQL database. It is a great way to avoid MySQL injections, and create some great MySQL permissions. Let's create a MySQL view named dept1, dept2, and dept3 so we can give every worker access to see only their department:

CREATE VIEW dept1 AS 
 SELECT 
  Dept,
  Manager,
  Name,
  Birthdate,
  Salary
 FROM
  payroll
 WHERE
  dept = 1;

CREATE VIEW dept2 AS 
 SELECT 
  Dept,
  Manager,
  Name,
  Birthdate,
  Salary
 FROM
  payroll
 WHERE
  dept = 2;

CREATE VIEW dept3 AS 
 SELECT 
  Dept,
  Manager,
  Name,
  Birthdate,
  Salary
 FROM
  payroll
 WHERE
  dept = 3;

Now that we have created the MySQL views, we can start to create all the users and grant them their MySQL permissions. To create a user it is very easy:

CREATE user Bob identified by 'password';
CREATE user Bill identified by 'password';
CREATE user Bart identified by 'password';
CREATE user Jim identified by 'password';
CREATE user Janet identified by 'password';
CREATE user Jack identified by 'password';
CREATE user Mary identified by 'password';
CREATE user Martha identified by 'password';
CREATE user Marty identified by 'password';

This will create all the users that are specified above, and will also assign them a password which in this case is password for everyone.

To grant MySQL permissions we need to decide what MySQL permissions we want to assign. If we want to assign MySQL permissions to do basically anything to the database we would assign them as so:

GRANT ALL PRIVILEGES ON *.* to Mary;

This will give Mary MySQL permissions to do anything she pleases to the database.

If we want to give UPDATE MySQL permissions to Bob for only certain MySQL database columns we can do so just like this:

GRANT UPDATE(Dept,Manager,Name,Birthdate) on RedFlameCompany.dept1 to Bob;

This will give Bob MySQL permissions to UPDATE the Dept, Manager, Name, and Birthdate columns on the dept1 MySQL table.

What if we want to grant view only MySQL permissions to someone? That will be as easy as:

GRANT SELECT(Dept,Manager,Name,Birthdate) on RedFlameCompany.dept1 to Bill;

Now Bill has MySQL permissions to SELECT or view the Dept, Manager, Name, and Birthdate columns on the dept1 MySQL table.

All we have left to do is to check the MySQL permissions of a user. We can do that with the SHOW GRANTS command:

SHOW GRANTS For 'Mary';

Which will output:

--------------
SHOW GRANTS For 'Mary'
--------------

+--------------------------------------------------------------------------------------------------------------+
| Grants for Mary@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'Mary'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This shows that Mary truly does have MySQL permissions to do anything she wants in the MySQL database.

The rest of my MySQL code can be found here. If you have any questions or comments feel free to leave a comment below.

If you liked this post, you can share it with your followers or follow me on Twitter!

MySQL Tips

I haven't posted in a while, but I have gotten a lot of homework done. Which is a really good thing. One of the classes I have gotten a lot done in is my Database Design class. It is a pretty laidback class for being an upper division course. I mean its course number is CS4307, which is pretty far up there. Anyways, what I want to share today is how to do some basic MySQL and then transition into some more difficult ones. First, here is a link to my Github Repo that has all my MySQL scripts for the class if anyone is interested.

One of the very first things I learned when I was learning MySQL was how to insert into a table. It is rather simple, you just need to know the name of your table, the column names, and the values you want to insert into your table. An example would be like this

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

MySQL isn't case sensitive, but it is good form to capitalize all the statements. This makes it much easier to read. I recently learned that if you are going to be filling every column, and you know the order you can simplify the above query to

INSERT INTO table_name VALUES (value1, value2, value3);

This is rather nice when you are creating a new record from an online form in PHP because you will usually be filling every column so you can just leave out the column names and save yourself a good amount of time and headache. Because I don't know about you but, I do know that for me MySQL queries can get long and confusing very fast.

Let's go into something more difficult union selects! These things are pretty tricky. I will be showing an example I did in class. My teacher had one suggestion for the class, and it was "Good luck everyone. When I do union selects I just fool around until I get the answer I want and don't touch it ever again!" Let's just say I was less than pleased with that sage advice. Anyways this is my example on how to do a UNION SELECT with the widely known spj database. Which can be downloaded at the bottom of the page here. What we are trying to get returned in this example is every city that has either a supplier or a job. A supplier can be found in the s table, and a job can be found in the j table. Thus we need to perform a UNION SELECT to get the cities that have a supplier or a job.

SELECT DISTINCT city FROM s UNION SELECT city FROM j;

Which will return us the statement of

--------------

+--------+
| city   |
+--------+
| London |
| Paris  |
| Athens |
| Rome   |
| Oslo   |
+--------+
5 rows in set (0.00 sec)

--------------

Which is exactly what we wanted. I am going to end this post here, but I'll post again about MySQL because this is nothing compared to what is still out there to discover! If you have any requests, such as Sub Selects, or Joins just comment below, and I'll focus more on them.

Thanks for reading!

Spotify

Sometimes I am asked what kind of music I listen to, and so I thought I would share some of my current favorite playlists. First of all, I use Spotify to satisfy all of my music needs. I can stream whatever I want, because wherever I go there is WiFi. I use it on my Phone, Laptop, Airplay it to my TV, and sometimes even on the iPad.

Some of my favorite playlists include:

  • Zach A playlist made by my wife just for me, if you are looking for Love/Country Songs you'll want this one.
  • Worded songs A playlist curated by the up and coming DJ Phillip Witherspoon. You'll find it is mostly newer alternative, but who doesn't like that?
  • At Work A playlist by Digster Playlists great for writing a ton of code.
  • Top 25 Another playlist by Phillip Witherspoon. It changes constantly based on new and upcoming music.
  • Have a Great Day! A playlist curated by Spotify that will make you happy no matter what.
  • good family music A playlist created by my good friend Joseph. If you are into indy music you'll like this one for sure.
  • Excellent Ish A playlist from yours truly!

If you have any playlists I should add to the list, please feel free to comment below.

Hello World

I'm Zach. I created this blog as a way to better document the cool stuff I am working on, and hopefully help someone else out along the way. If you have any suggestions, or comments please leave them in the comments section. Cool projects on Github.