Importing CSV Into MySQL

Posted by Nessa | Posted in | Posted on 08-12-2007

11

The newer versions of phpMyAdmin for some reason no longer include the option to import CSV files. If you are trying to import an Excel or other delimited file, first make sure that it’s in CSV format (with Excel you can export it as CSV). Then log into phpMyAdmin or your MySQL prompt and run this command:

load data local infile ‘/path/to/file.csv‘ into table yourtablename
fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’;

With phpMyAdmin this is done within the ‘SQL’ section, indicated by the ‘SQL’ tab.

How to Upgrade to a Non-Existent MySQL Version

Posted by Nessa | Posted in , , , | Posted on 28-11-2007

15

Working in webhosting for a while now I’ve had some people ask for really weird shit, and I’ve dealt with a lot of people who try to sound a lot smarter than they actually are (I’m one of them). The latest of the bunch is a guy who asked for MySQL 7.0 claiming that he’s a MySQL programmer and that he specially programmed his database to work with MySQL 7.0. He really didn’t take it to heart very well when I told him that there is no MySQL 7.0 and the most he can hope for is 6.0x alpha (FYI for future readers a year from now, read the damn date on this post). Besides the point, the guy apparently felt like I was talking down to him so he went out of his way to mention that because he has a bachelors degree in computer science and that he’s an avid Microsoft Word user, he definately knows more than I do when it comes to doing my job. So, I gave in and agreed to upgrade him to MySQL 7.0.

The trick of the trade here is that you can essentially install any version of MySQL that you want to, whether it exists or not! It’s a long-standing suck point in cPanel that the MySQL version in user cPanels are read from a static file within the datastore directory:

/home/username/.cpanel/datastore/_usr_sbin_mysqld_–version

Within that file is the output of the ‘/usr/sbin/mysqld –version‘ command, which cPanel reads and outputs to each user’s cPanel. You can easily edit this file in one user’s account to make it read whatever MySQL version you want:

Needless to say, after I ‘upgraded’ his version to 7.0, he claims his scripts started working!

Installing Sphinx for MySQL 5.1

Posted by Nessa | Posted in , , | Posted on 21-07-2007

19

This is probably one of the two things that drove me crazy over the last two weeks. The first was trying to get cPanel to stop being such a MySQL nazi, the second was getting Sphinx installed into MySQL 5.1 for one of the clients. I’ve installed it with 5.0 on Ubuntu before, but 5.1 with cPanel can be pretty torturous. After talking to the developer who was able to fill in the blanks, I’ve decided to write my own documentation on how to install Sphinx as a dynamic MySQL plugin for an existing MySQL 5.1 installation.

Before we start...

You need MySQL 5.1 installed to use the plugin feature. If you’re running on cPanel or other version-dependent software it’s a really really bad idea to install MySQL from source, so you’ll probably want to read this as a guide to upgrading via RPM. Also, this walkthrough is specific to MySQL 5.1.20 (beta), since that’s the latest release out at the time of this writing.

You also need to have root access, and a decent knowledge of Linux and MySQL.


Download the Sphinx binaries and MySQL 5.1.20 patch:


wget http://sphinxsearch.com/downloads/sphinx-0.9.7.tar.gz
tar -xvzf sphinx-0.9.7.tar.gz
cd sphinx-0.9.7/mysqlse/
wget http://v-nessa.net/imh_files/sphinxse097-mysql5120.patch


patch -p1 < sphinxse097-mysql5120.patch

I should point out that Andrew Aksyonoff (the developer) provided that patch to make Sphinx compatible with MySQL 5.1.20, but you’d probably have to check back on his site for version ugprades and such, esp. for newer versions of MySQL.

Now download the MySQL 5.1 sources — these are only going to be used to compile the Sphinx module:

cd /usr/src
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.20-beta.tar.gz/from/http://mysql.he.net/
tar -xvzf mysql-5.1.20-beta.tar.gz
cd mysql-5.1.20-beta
cp -Rf /usr/src/sphinx-0.9.7/mysqlse/ storage/sphinx

Compile the Sphinx module against the MySQL 5.1 sources, but don’t install:

sh BUILD/autorun.sh
./configure
make

If you look in storage/sphinx/.libs you’ll see the loadable .so files that can plug in to MySQL easily. I suggest you copy them into a more permanent location:

mkdir /var/lib/mysql/plugins
cp storage/sphinx/.libs/ha_sphinx.* /var/lib/mysql/plugins

Now add this line to /etc/my.cnf and restart mysql:

plugin_dir=/var/lib/mysql/plugins

To install, log into the MySQL root and issue the ‘INSTALL PLUGIN’ command:

mysql -u root
mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

To verify its installation, just run the ‘SHOW ENGINES’ command:

mysql> show engines;
| CSV | YES | CSV storage engine
.....
| SPHINX | YES | Sphinx storage engine 0.9.7


If you need further info, check out the doc-u-men-ta-tion.

Upgrading to MySQL 5.1.x on cPanel

Posted by Nessa | Posted in , , | Posted on 20-07-2007

13

Update 2/18/14: Hi Guys.  This post is no longer relevant as of…a really long time ago.  I wrote it in 2007, and a lot has changed.  If you’re trying to upgrade to a version of MySQL that cPanel doesn’t yet support, check out my article here, which has updated instructions.  This post is being left intact for archiving purposes only.

If you have server that run on cPanel, you’ll probably know how big of a Nazi it is in regards to the MySQL versions it can run. We just got this new line of servers at work and one of them I was pretty heartset on installing MySQL 5.1, mainly because of its loadable plugin features where you can install a plugin or module without having to recompile the whole damn thing. Upgrading to 5.1 is easy, you just have to follow the right steps.

First, I would recommend upgrading to cPanel 11 or EDGE, which should have support for compiling Apache with non-supported versions of MySQL. On this server, I’m currently running 11 on the bleeding edge build. Also, it’s a splendid idea to dump all your databases before upgrading.
Note that these instructions mention MySQL 5.1.20-beta because that’s the latest release available at the time of my writing….but you can essentially follow this guide for any version of MySQL!

1. Copy the MySQL libraries from the server into a temporary location:

mkdir /root/sqllibs
cp /usr/lib/libmysqlclient.* /root/sqllibs

2. Find any installed MySQL packages:

rpm -qa | grep -i mysql-

This should present a list of installed rpm’s…remove them with rpm -e ,but note that some may need to be removed before others. Some people also would remove the /var/lib/mysql directory, but you can leave that there.

3. Download and install the MySQL 5.1.x packages:

Hop on over to http://dev.mysql.com/downloads/mysql/5.1.html#linux-x86-32bit-rpms and download the 5.1 RPM’s and install them:

rpm -i MySQL-client-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-devel-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-embedded-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-test-5.1.20-0.glibc23.i386.rpm
rpm -i MySQL-server-5.1.20-0.glibc23.i386.rpm

4. Prepare cPanel

You’ll want to make sure that cPanel’s updates don’t reset the MySQL version, so you need to run the following commands to force cPanel to skip MySQL updates:

touch /etc/mysqldisable
touch /etc/mysqlupdisable

Now edit /var/cpanel/cpanel.config and change the MySQL version to 5.1

Create the symlink:

ln -s /var/lib/mysql/mysql.sock /tmp

Also, verify that the MySQL version is correct by running mysql -V

root@vps [~]# mysql -V
mysql Ver 14.13 Distrib 5.1.20-beta, for pc-linux-gnu (i686) using readline 5.0

5. Set up MySQL

MySQL should have already been started at this point, so you can attempt to log in as root using mysql -u root . If you are able to log in on the first try, great. If not, you’ll need to reset the MySQL password:

pico /etc/my.cnf

Add this line, and restart MySQL

skip-grant-tables


service mysql restart

Now log into MySQL root and set the password:


mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit;

service mysql restart

When you’re done, remove the skip-grant-tables line from /etc/my.cnf and restart MySQL. Then log into Webhost Manager and reset the password *again*… this is necessary to build a bridge between the linux root user and the MySQL root user, so you can log into MySQL both through WHM’s phpMyAdmin, and SSH without a password when logged into the server as root.

All you need to do now is recompile Apache, but move the MySQL libraries back so easyapache can find them:

mv /root/sqllibs/libmysqlclient.* /usr/lib/mysql/

I haven’t tested too many configurations yet, but what works for me is Apache 2.2.4 with php 5.2.3, compiled with MySQL and mysqli, but NOT system MySQL.

Simple MySQL Search Query

Posted by Nessa | Posted in , , , , | Posted on 04-07-2007

9

If you use MySQL to keep a ton of records, it might be nice to be able to search for the particular entry you’re looking for via a simple form on your site. To set this up, we’ll make two scripts — one being the form itself, the other being script that executes the MySQL query.

In this example I created a simple form to query a database to look a person’s last name from a database column in an ‘addressbook’ database. First, we need to create the form. This is just a simple html file with a single input field:

<html>
<body>
<h4>Enter Last Name:</h4>
<form action="query.php" method="post">
Server: <input name="lastName" type="text" />
<input type="submit" value="Submit" />
</form>
</body>
</html>

Here i named the form field ‘lastName’, which will be the variable passed on to the php script and returned later on, and that the form action is set to ‘query.php’, which is the name of the script processing the form. Next, create a file called query.php:

In the first section we need to define a few database variables to allow the script to connect to the database:


<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";

Next, we need to define the posted variable ‘lastName’, which we created in the form to allow that variable to pass into this script. If you have register_globals turned on (which is a BAD idea), you don’t need to do this.


$lastName = $_POST['lastName'];

Now for actual query itself. The syntax you use to search a database table is as follows:

SELECT <what info> FROM <table> WHERE <column>='<search term'>

So in that case, I want to select everything from the ‘names’ table where the last name is equal to what I search for, denoted by the variable ‘lastName’

$query = "SELECT * FROM names WHERE lastname='$lastName'";

Similarly if you wanted to search two tables in one query you can just use the UNION command like so:

$query = "SELECT * FROM name WHERE lastname='$lastName' UNION SELECT * FROM morenames WHERE lastName='$lastName'

Now that all that crap is defined, create the database link:


$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>

Now you can echo the results back into an array (in case there is more than one entry):

<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>

To explain above, the query is run against the database and the results are fetched as an array. The row(s) contained the search terms are then displayed to the screen based on the colums specified, which in this case are ‘firstName’ and ‘lastName’
In case you’re on the slower end, here’s the entire query.php script:

<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";

$lastName = $_POST['lastName'];

$query = "SELECT * FROM names WHERE lastname='$lastName'";

$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>

<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>

How to Make a Sexy Tag Cloud with PHP and MySQL

Posted by Nessa | Posted in , , , , | Posted on 12-02-2007

33

Tag CloudWell it seems that everyone has one, and I’d have to admit that a tag cloud is a good way to spice up your site a little bit. I first thought of this when setting up a friend’s site… he wasn’t using a CMS like WordPress or anything that I could find a quick tag cloud plugin for, so I figured I could probably just make my own. Well, I did and now I shall share it.

This tutorial will show you how to set up a simple tag cloud using PHP and MySQL, with a little bit of Ajax effects
Before we get started, take a quick look at the sample cloud.
Read the rest of this entry »

The Basic MySQL Injection

Posted by Nessa | Posted in , , , | Posted on 17-01-2007

7

Ahhhh the classic hack that doesn’t work anymore… which is why I’m posting it here. I always thought it was kind of an interesting concept but no one ever made it simple for me, so I shall do this for you.

How to do a simple MySQL Injection

Ok, so this is your basic PHP login script that asks for your username and password, which would then query the database to authenticate you:

$user = $_POST["username"];
$pass = $_POST["password"];
$query = mysql_query(“SELECT * FROM users WHERE user=’$user’ AND password=’$password’”);
$rows = mysql_fetch_row($query);
if ($rows == 0) {
die (‘Login Incorrect!’); }

Assuming that register_globals are enabled on the server, this script will work and in return use the POST variable to query the database for an already-defined row to see if both conditions are being met, which are obviously the username/password fields. If the input does not meet this requirement, then the connection dies and returns the “Login Incorrect” error. So assume I log in with the username “nessa” and the password “sexy.” The $query string will pass this command to MySQL:

$query = mysql_query("SELECT * FROM users WHERE user='nessa' AND password=" OR"=' OR '1'='1'");

Since I used the OR clause in the password field, that can leave a few possibilities up to the database to determine whether a statement is true or false. As you can see, will always be equal to , and 1 is always equal to 1, so MySQL is happy as long as these requirements are met.

So what does that tell you? You can easily replace either the username or password fields withe a or a ” OR 1 and you will have a successful login each time. Of course there are a lot more combinations that will work — you might want to check out this site:

http://www.justinshattuck.com/?p=156&akst_action=share-this

Now seeing that this site is powered by PHP and MySQL, you probably think I’m stupid by posting this. Well quite frankly, MySQL injections are old and nearly impossible with well-scripted PHP software and good PHP environment. If you’re running a custom script or old software, here’s how you can protect your crappy software from being exploited:
Check your magic quotes setting in php.ini or .htaccess:

magic_quotes_gpc should be turned on, as this automatically slash-escapes your codes so MySQL is less likely to make a false positive. As of PHP4, this setting is enabled by default.

If you don’t want to use magic quotes, use mysql_real_escape_string():

Here’s a simple script you can use as an include to automatically escape null characters:

// Quote variable to make safe
function quote_smart($value
)
{
// Stripslashes
if (get_magic_quotes_gpc
()) {
$value = stripslashes($value
);
}
// Quote if not integer
if (!is_numeric($value
)) {
$value = “‘” . mysql_real_escape_string($value) . “‘”
;
}
return
$value
;
}
?>

And the obvious, if you’re using bundled software make sure you keep it up to date. New exploits are being found all the time, so don’t put yourself out there by not updating your shit.


Messing with PHP and MySQL

Posted by Nessa | Posted in , | Posted on 16-01-2007

0

This just a simple tutorial on how to connect to MySQL with PHP, as well as using a MySQL query to create simple database.

The first thing you would want to do is create the database so PHP can access it. My preferred method is through phpMyAdmin or cPanel (if your host provides this)…or you can run a simple query via MySQL command line:

CREATE DATABASE `liquor`;
USE `liquor`;
CREATE TABLE `brands` (
`id` int UNIQUE NOT NULL,
`brand` varchar(40),
`type` varchar(50),
PRIMARY KEY(id)
);
INSERT INTO cars VALUES(1,'skyy','vodka');
INSERT INTO cars VALUES(2,'cpt. morgan','rum');
INSERT INTO cars VALUES(3,'ice 101','schnapps');

This code will basically create a database named “liquor” with the table “brands” that lists three of my favorite liquors in order with the type of liquor that they are. From here, you’ll want to create a database user and add it to the database with the appropriate privileges. You can usually do this in your MySQL management interface provided by your hosting company, but if not you can check this site for information on users and privileges.

Now for the PHP code, you declare your database variable and use to mysql_connect function to establish a database connection:

<?php
$db_user = "user_name";
$db_pass = "password";
$db_host = "localhost";

//connection string
$dbconn = mysql_connect($db_host, $db_user, $db_pass)
or die("Cannot connect to database");
echo "Connected to the database!<br>";
?>

Once you’ve verified that you can connect to the database, you now need to tell PHP what table you want to work with. Let’s use the one we just created:

<?php
//select a database to work with
$selected = mysql_select_db("brands",$dbconn)
or die("Could not select liquor");
?>

Now here’s the complex part of the code where we create a loop to pull the data from the three rows we created:

<?php
//execute the SQL query and return records
$result = mysql_query("SELECT id, brand,type FROM brands");
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
echo "ID:".$row{'id'}." Name:".$row{'brand}."
".$row{'type'}."<br>";
}
?>

Of course, this example is just a dummy one that returns the same records we’re querying –so it’s not really useful in real life.

Once you have the information you need from the database, try to make it a habit of closing the connection. Using persistant connections can cause issues on the server and is not recommended for a live production site.

<?php
//close the connection
mysql_close($dbconn);
?>

phpacademy.org Offers FREE PHP Tutorials

Posted by Nessa | Posted in , | Posted on 31-03-2011

4

There’s a new site out there that’s offering high quality PHP video tutorials – for FREE!  All you PHP gurus, check out phpacademy.org.  They currently have over 200 PHP and MySQL tutorials for both beginner and intermediate users.  Unlike other PHP tutorial sites, phpacademy is unique because the tutorials are all on video, so there’s less boring reading.  Did I also mention that it’s free?

Head on over and check them out, feel free to post comments and reviews here!

WordPress Thinks Network Solutions is Stupid

Posted by Nessa | Posted in , , , | Posted on 22-04-2010

6

Quick quiz: What does a hosting provider do when they know they’ve messed up and don’t want to deal with the fallout?

You apparently blame WordPress.

Don’t get me wrong here – being behind the scenes of server management for a webhosting company makes you the target of a lot of accusations.  And yes, most of the time when a user’s site gets hacked it’s their own damn fault. But in this case, Network Solutions is apparently trying to push their issues off on WordPress because they don’t want to admit they f***cked up.

Well, WordPress is pissed.  I logged into my dashboard today and the first thing I see in my news feed is:

A web host had a crappy server configuration that allowed people on the same box to read each others’ configuration files, and some members of the “security” press have tried to turn this into a “WordPress vulnerability” story.

To highlight the best part:

I’m not even going to link any of the articles because they have so many inaccuracies you become stupider by reading them.

P.S. Network Solutions, it’s “WordPress” not “Word Press.”

Burned.

In short, Network Solutions acknowledged that most of the problem was due to users’ public_html and wp-config.php files being readable by other users on the server – something which could have easily been caused by the users setting the permissions of those files insecurely. But they took a shot in the dark and said that the problem was caused by WordPress putting cleartext database credentials in the wp-config.php file – something that just about every software developer does, as WordPress states:

WordPress, like all other web applications, must store database connection info in clear text. Encrypting credentials doesn’t matter because the keys have to be stored where the web server can read them in order to decrypt the data. If a malicious user has access to the file system — like they appeared to have in this case — it is trivial to obtain the keys and decrypt the information. When you leave the keys to the door in the lock, does it help to lock the door?

Good point. They also went on to say that a properly configured web server will not allow users to access the files of another user, regardless of file permissions. This is why most hosts have switched to using suPHP or phpsuexec, a technology which Network Solutions was apparently left in the dark on. At least now they seem to be taking responsibility for the problem and are attempting to handle it.

I’m also going to state, based on comments in popular blogs from users that don’t know what the hell they’re talking about, that unless someone has access to view the source of a PHP file, they can’t see the database credentials. PHP files are executed server-side, and only their output is sent to the browser. Since the username and password are stored as variables and are not echoed out anywhere, someone simply calling wp-config.php from a browser can’t access your login data.

You’re probably going to find all kinds of fixes on various sites that this story is covered on, but I’m going to give the same advice I do for all my customers that have had sites hacked:

  • Change your FTP and MySQL user passwords
  • Replace all files on your site from a ‘clean’ backup
  • Make sure the software on your site is up to date
  • Scan your PC for viruses
  • Choose a secure host

Remember that your site can get hacked regardless of who your host is or how secure they are, though your host has to take some level of responsibly for hacks that are caused by their own bad configuration, such as in the case with Network Solutions.