RSS
 

Archive for the ‘PHP’ Category

Responsible SQL: How to Authenticate Users

09 Nov

Most SQL-injection articles set a horrible example for young programmers.

Here is a very typical “bad example” of why you need to escape user data before it goes into SQL queries:

(ed. The symbol « is a line break that’s not in the real code.)

  1. $username = $_POST[‘username’]; // username=admin
  2. $password = $_POST[‘password’]; // password=’ OR 1=1; — ‘
  3.  
  4. $user = $db->query("SELECT * FROM users WHERE «
  5.           username=’$username’ AND «
  6.           password=’$password’ LIMIT 1;");

The point, of course, is that you must sanitize your user input, or else this person would run this query:

  1. $user = $db->query("SELECT * FROM users WHERE «
  2.           username=’admin’ AND «
  3.           password = ” OR 1=1; — ‘ LIMIT 1;");

Which grants the sneaky user all your admin privileges. Other versions have nefarious users dropping your users or articles tables.

The problem is: this is the wrong way to authenticate users. These examples are written for beginners to understand the importance of sanitizing input, but they also provide a model to those beginners for how user authentication works. And it’s a very bad model.

This is a long one, more after the break. Read the rest of this entry »

 
Comments Off

Posted in Database, MySQL, PHP

 

Help Me Scale

06 Jun

I’ve been reading Eran Hammer-Lahav’s intelligent posts on microblog scalability, and now I’m concerned about my own “microblog” site, Picofiction.

Similar to social networks, social updates, social messaging, social… Like many social web sites—amongst our weaponry…—Picofiction lets you “follow” your favorite authors, displaying all their posts along with yours.

I handle this very naïvely: everything is offloaded to the database. There are three tables involved here, one of users, one of posts, and one of follower/followee bindings.

Here’s the basic structure of this query:

SELECT post_id, post_body, post_date, post_type,
  user_name AS author_name, user_id AS author_id
FROM posts
LEFT JOIN users
ON posts.author_id = users.user_id
WHERE author_id = 'CURRENT_USER'
OR author_id IN (
  (SELECT followed_id
   FROM followers
   WHERE following_id = 'CURRENT_USER')
  )
ORDER BY post_date DESC
LIMIT PAGE_START,20;

Here’s where I need help: this works great on a single database, but it does not scale horizontally.

Since this horizontal scalability is such a hot topic right now, I’m asking for ideas. I’d like to put in the infrastructure before there is a need for it.

Eran points out that caching is not as simple a solution as we’d like to think. What do you cache? How do you keep caches in sync?

Does anyone have experience with MySQL Cluster Servers? It seems like the best way of scaling is to make the process as parallelizable as possible. The database then handles the parallelization, so the less I can do in the program the better, right?

 
Comments Off

Posted in Database, MySQL, PHP

 

How to: Upgrade or Recompile PHP on RHEL5 (Outdated)

03 Nov

Update: This post is nearly two years old, and this is not how I would recommend upgrading PHP on RHEL, yet it continues to get traffic. If I can get my hands on a copy of RHEL, I’ll update this (or I might try using Fedora just to compare).

Upgrading PHP on RHEL 5 is difficult. Having done it on several servers, I’ve gotten it down to a 15 to 20 step process. It takes a while, but it’s straightforward. I thought I’d share, because help was sparse and noncontiguous at best.

RedHat Enterprise Linux: Hard to upgrade PHP.

RedHat Enterprise Linux 5 comes with PHP 5.1.6 and, as of this writing, this is the highest version available on yum. If you want to upgrade to 5.2.4, or even recompile 5.1.6 with a custom configuration, you’ll need to resolve several dependencies, first.

Unless otherwise specified, whenever I run ./configure, I always include --enable-shared and --enable-static.

The first step is to make sure you have a working APXS script installed. None of the servers on which I’ve done this had it. I installed Apache 2.2.4 over the default install, since it was the latest version. Be sure to enable APXS with --enable-so. Be careful configuring Apache, as it likes to install itself in /usr/local/apache2/ instead of /etc/httpd/, which you may prefer.

Now we start resolving the dependencies. I’d start with libtool and libiconv. The former you should be able to install via yum. The latter you may have to download, and after you configure it, from the source directory copy m4/iconv.m4 to /usr/local/share/aclocal/iconv.m4.

Use yum to make sure mysql-devel is installed, you’ll need it to link to mysql.

Then I’d do the image manipulation software, since it’s fairly easy. Use yum to install libjpeg, libpng and freetype. You can then use yum to make sure both gd and gd-devel are installed.

I installed libmcrypt, libmhash, and ming at this point. I’d say it’s a good time to get any of these more particular dependencies out of the way. I also installed Tidy, which you need to check out from their CVS repository. You can run build/gnuauto/setup.sh from the Tidy source directory to create the autoconf files.

Now we get to the crux of the matter: configuring PHP. All the major dependencies should have been taken care of. If you have other PHP options you’ll need, make sure those prerequisites are installed, as well. Run the configure script in the PHP source directory with everything you need enabled. I find it helpful to create a script like php.config with the following format:

'./configure' \
'--with-cgi' \
'--with-fastcgi' \
'--with-gd' \
...
'--with-xml' \
"$@"

You need to include the slashes \ at the end of every line. The last line, "$@" makes the script output the output of configure.

If you get an error running make, you may need to edit your Makefile. Find the EXTRA_LIBS section (in vi/vim, type <ctrl-c> /EXTRA_LIBS <return>) and add -liconv to the end of the line. Then try make clean && make and it should work.

You may or may not have to edit your httpd.conf, after running make install from the PHP source directory, to add the AddType or AddHandler directive for PHP.

That should be it. You can install extensions via PECL or Pear and everything should run. Save the source directory and your php.config (or config.nice) file, and you’ll be able to recompile at any time, in case you forgot something. (I, for example, forgot to add --with-mysql the first time!)

Let me know if you run into other problems. Most can be solved by typing yum install ###-devel to resolve a dependency, but if not, I’ve done this enough to be of some help.

 
2 Comments

Posted in PHP