Tag Archives: mysql

Bobby-Tables.com: A guide to preventing SQL injection

Apr 11, 2012 by Matt | Posted in Coding 2 Comments

SQL injection: still one of the more common exploits against web applications, and yet it is one of the easier ones to guard against. Unfortunately, bad tutorials often teach newbie developers bad habits that enable injection attacks, rather than using more secure methods of interacting with databases.

Yes, PHP often gets a lot of flak for this, as its status as an introductory languages makes for a greater number of subpar coders and introductory tutorials, but SQL injection is by no means a “PHP thing.” Actually, there are probably more resources warning about SQL injection for PHP than other languages…

Wouldn’t it be useful if there was one resource you could point people to in order to explain SQL injection and show some examples of best practices in their language of choice?

Bobby-Tables.com has examples of safe methods to handle database interaction in several languages, including PHP, Python, C#, Ruby and Java. Plus, it gets bonus points for referencing the relevant XKCD comic. (There really is one for every occasion, isn’t there?)

How to Import a Very Large SQL Dump with phpMyAdmin

Sep 26, 2011 by Matt | Posted in Software & Scripts 5 Comments

Having trouble importing a large SQL dump through phpMyAdmin? This is a problem that often plagues users of shared web hosting, when they’re moving to a new host or restoring a backup. Since most shared hosting providers don’t offer shell access, you have to use a tool like phpMyAdmin to upload and load your SQL file.

Unfortunately, phpMyAdmin often has trouble processing large files. Sometimes it takes so long for the process to run that it times out, or other times the upload quota is simply too low.

Fortunately, there’s a simple solution: split your SQL file into smaller chunks. Here’s a command you can use under Mac OS X or Linux to do it automatically:

split -l 5000 ./path/to/mysqldump.sql ./mysqldump/dbpart-

The split command takes a file and breaks it into multiple files. The -l 5000 part tells it to split the file every five thousand lines. (You can tweak this value to find a good medium between fewer files and larger sizes.) The next bit is the path to your file, and the next part is the path you want to save the output to. Files will be saved as whatever filename you specify (e.g. “dbpart-“) with an alphabetical letter combination appended.

Now you should be able to import your files one at a time through phpMyAdmin without issue.

Getting Your Feet Wet with PDO and Migrating Old MySQL Code

Aug 5, 2011 by Matt | Posted in Coding, Featured One Comment

You may have heard that the old MySQL extension for PHP is going to eventually be deprecated in favor of the newer (and potentially more secure) MySQLi and PDO extensions. You’re going to need to update your old code sooner or later, so why not make it sooner?

I’m going to recommend PDO over MySQLi simply because it’s available on more systems, and it’s syntax may be a little bit easier to learn for newbies. PDO has been bundled with the main PHP distribution since PHP 5.1, and has been in PECL even longer, while MySQLi has only been included since 5.3. Whichever you use is up to personal preference and project requirements of course, but I will be sticking with PDO for the duration of this tutorial.

Continue reading →

Automatic Amazon S3 Backups on Ubuntu/Debian

Mar 15, 2010 by Matt | Posted in Hosting 2 Comments

If you manage your own web server, as you do with a VPS, one thing you need to look into is a backup strategy. It wouldn’t be pleasant for your files to vanish into the ether in the event of some sort of catastrophic server meltdown, would it? Optimally you want to, on a daily basis, offload a copy of everything important to a separate geographical location. One excellent way to do that is to follow Pro Blog Design’s new tutorial on how to automatically back up your files and databases to Amazon S3.

S3, or Simple Storage Service, is Amazon’s cheap cloud data storage system. Michael Martin, the author of the tutorial, says that his bill from last month was $2.60. ($0.15 per month per GB for stored, $0.15 per GB transferred.) Using a backup script on your server, you can automatically archive and encrypt your files and MySQL dumps, then send them off to Amazon’s servers for safekeeping.

I should start by saying that while s3 is not a free service, it’s incredibly inexpensive! My bill for the last month was $2.60, and that was with backing up a lot more than just this site! It’s the cheapest peace-of-mind ever.

Automatic Amazon S3 Backups on Ubuntu/Debian [Pro Blog Design]

An API for the Web: Learning YQL

Jan 26, 2010 by Matt | Posted in Coding No Comments

I just read one of the most interesting articles Net.Tuts+ has published in the last few months: An API for the Web: Learning YQL.

Web apps and web services multiply like rabbits. They’re all fun to play with (like rabbits) and fun to integrate into other projects (unlike rabbits). But learning a new API every other day isn’t feasible or fun. And that’s the problem the Yahoo Query Language (YQL) is out to solve.

Think of YQL as the API for the web, the one API to rule them all. It’s not a hard one to learn, so let’s get you up to speed right now!

I couldn’t have said it better myself. YQL is just that: a wrapper for other APIs. It makes it easy to gather data from virtually any API, mash data up if necessary, and bring it into your own application. With generous daily query limits, and no commercial usage restrictions, what’s not to like?

A basic query to collect tweets from two twitter accounts would look like this:

SELECT * FROM twitter.status.timeline.user WHERE id in ('redwall_hp','fantasyfolder')

Very much like an SQL query, no? And there are “tables” to obtain data from many sources, such as Delicious, Netflix, Facebook, Flickr, Github, Last.fm, RSS feeds, etc.. Is there not a table for an API you need to use? You can create one.

Result sets can be returned as XML or JSON.

Developer tools like this and the BOSS search API, which is one of the available tables in YQL, are what I believe will keep Yahoo alive in the coming years.

How to Secure Your WordPress Installation

Nov 18, 2009 by Matt | Posted in WordPress No Comments

Digging into WordPress has recently published a new article on securing WordPress. It covers setting up optimal database privileges, adjustments you can make to your wp-config.php file, changing the table prefix, changing the default admin username, and a couple other things.

Of course, when it comes to the security of your WordPress site, these techniques are merely the beginning. As you continue in your WordPress travels, you will discover many, many more ways to increase the security of your site. By implementing the methods presented in this article during the setup process, you will be strengthening the security of your site’s foundation, providing yourself a solid platform on which to build.

Definitely worth a read if you haven’t already looked into the techniques.

How to Secure Your New WordPress Installation [Digging into WordPress]

MySQL Prepared Statements With PHP

Feb 25, 2009 by Matt | Posted in Coding One Comment

You may already be familliar with the problem of MySQL Injection, a common exploit that can cause irreparible damage to a database.

You may not be aware of it, but there is an alternative to the “mysql_*” function set. The mysqli functions, for “MySQL Improved” are used in much the same way as their predecessor, but they have some advantages. One such advantage is “Prepared Statements,” a method of preparing a query that separates the data from the syntax.

Prepared Statements are a little harder to use, but they are more secure, and arguably easier to write and maintain.

Prepared Statements in PHP and MySQLi

Instead of grabbing and building the query string using things like $_GET[‘username’], we have ?’s instead. These ?’s separate the SQL logic from the data. The ?’s are place holders until the next line where we bind our parameters to be the username and password. The rest of the code is pretty much just calling methods which you can read about by following some of the links at the end of the article.

PHP Tip: Prevent SQL Injection

Jan 27, 2009 by Matt | Posted in Coding No Comments

SQL Injection is one of the most common exploits. It’s a sneaky technique that takes advantage of unsafe database querying practices to gain access to the database.

Suppose you have a input form that asks for an email address for a newsletter subscription. The data is passed to the script, which inserts the data with the following:

$input = $_POST['email'];
mysql_query("INSERT INTO emails (email) VALUES('$input')");

Looks fine at a glance, doesn’t it? Well, it would if you’re new to the horrors of SQL injection. Note that the form field’s data is passed right along without any validation. That is not good. Some contempt-worthy person could come along and type something like this into the form:

Continue reading →

8 Useful WordPress SQL Hacks

Dec 23, 2008 by Matt | Posted in WordPress No Comments

Smashing Magazine has done it again. Their latest post, 8 Useful WordPress SQL Hacks, is a goldmine of useful tricks to streamline your WordPress experience.

The tips include

  • Backing up (and restoring) your database
  • Batch deleting post revisions
  • Resetting a lost admin password
  • Updating your database with a new domain, if you ever move to a new one

Definitely some knowledge to have your blogger’s toolbox.

If you really want to master SQL, I’d recommend reading Learning MySQL or similar book.

Learning MySQL [Book Review]

Oct 8, 2008 by Matt | Posted in Coding No Comments

Almost anyone who’s played around with PHP before has run into the subject of databases. MySQL databases are the most common method of storing massive amounts of data to later be sorted through and retrieved for display via script. WordPress stores all of it’s posts and settings in MySQL tables, forum scripts are powered by databases, sites like Amazon, YouTube, and of course IMDB make extensive use of databases. If you think about it, most modern websites are just pretty user interfaces for databases.

As common as they may be, databases sure are mysterious critters. It look me awhile to grasp the concept of them at first, and even longer for me to pick-up the skills required to make use of them. In addition to knowing a scripting language like PHP or Perl, and how to submit a query to the DB server, you also need to know the SQL language.

I just finished a great book on MySQL. Learning MySQL by Seyed M.M. Tahaghoghi and Hugh Williams is a comprehensive and well-explained book that teaches you from the ground up about MySQL databases and how to work with them. It introduces the concept of a database, walks you through installing the MySQL server software (if you’re not already running it), explains querying, then moves on to the real meat of the book: Structured Query Language, or SQL. It covers basic SELECTS and INSERTs, JOINS, nested queries, table and column types, and really everything you need to know to get started.

Following chapters cover topics like using PHP or Perl to interact with databases (as opposed to using a MySQL prompt) and securing web applications. There are also sections on planning database structures optimally, optimizing performance.

The book is written in a manner that should make it easy to follow, it’s full of code examples to try out, and overall is enough to give you a general to intermediate knowledge of MySQL. It also works as a handy reference.