SQL Injection Security Issues

Related: where do people find out about what security holes there *are* in PHP, and how to close them? The only one I've ever heard about is this injection attack, and making sure you use $_POST instead of register globals.

There was a http://developers.slashdot.org/article.pl?sid=04/04/27/1358240 yesterday on this topic. A couple of highlights: http://developers.slashdot.org/comments.pl?sid=105564&cid=8985253 http://developers.slashdot.org/comments.pl?sid=105564&cid=8984484

MySQL recently implemented support for stored procedures. It's a much better architecture than sending sql strings through commands in web scripts - for lots of reasons including offering better protection against sql injections.

I'm not much of a PHP expert, but I think you'd probably be better off using mysql_escape_string, which is closer to perl's $dbh->quote(), and escapes more than addslashes. gramcracker: Try http://search.securityfocus.com/cgi-bin/swsearch/swish.cgi?query=php&metaname=alldoc&sbm=archive%2F1%2F&start=0.

http://ask.metafilter.com/mefi/6855#138603 can be a pain to implement at first, but it's a very good practice. If you're limited in the number of user accounts you can create, at least give them strong passwords and the bare minimum of privileges they need to do their jobs. People find security holes by looking for them and by stumbling upon them. I wouldn't think of SQL injection as one attack; it's more like a whole realm you need to be cautious of. Using $_POST instead of making register_globals do the heavy lifting is good, but I always take it a step further and immediately assign the posted value to a variable. That way it's locked away in a cage and can only do what I let it do (so if I'm sloppy and "let" it do something nasty by not preventing that behavior, that's my fault). On preview: stored procs aid security, make things faster and make applications easier to maintain, so now I just have to wait until providers start installing MySQL versions that support them. Anyone have a good reference for MySQL stored proc syntax (other than the manual-- just looking for a favorite bookmark or something)?

yerfatma, not very detailed, but a start http://builder.com.com/5100-6388_14-5178706.html.

alphanerd - Agreed, running your scripts so that you give programmatic access to the DB super user is bad. But probably not helpful in my context. I'm writing something that I want to distribute. Specifically for web logs. A solution that requires bloggers to create special MySQL users may be a non-starter. Sad. But true. If I can lock it down without it that would be much preferred. gramcracker - yerfatma's link #2 is pretty much it. However, it's not php that has security holes, it's the code you write that has security holes. So having a proactive attitude is better than a hard and fast list. normy - Yep. Stored procedures are better. But I'm not using them. Part laziness, part preference. Again, if I can avoid going that route it would make me happier. Part of my problem is that I don't know what I'd fix in a stored procedure that I'm not fixing in the function above. fvw - You are correct sir. Thanks. Part of my problem is that this will be used by people to legitimately add huge blocks of text to the database. And these text blocks may legitimately contain valid SQL.

Here's what I use, if you're interested. /* Takes a format string and an list of arguments and substitutes the format codes in the format string for the elements of the arguments list. Similar to vsprintf, but specialized for generating SQL queries. Format codes are denoted by a percent sign (%) followed by another character. To insert a literal percent character into the output string, escape it (%%). Each successive format code is replaced by its corresponding argument in the list. %%: Replaced by a literal percent sign. %s: The argument, which must be a string, is used verbatim. %S: Replaced by the argument, which is escaped with addslashes. %q: Use for quoted strings. Like %S, but the value is also surrounded with quote marks. %l: Use for LIKE strings. Like %q, but the _ and % characters are also escaped. %d: The argument, which should be numeric, is used verbatim. Non-numeric arguments will be converted into numbers first. %n: Like %q, but empty strings are replaced with NULL instead of ''. %t: Like %n, but converts the result to a timestamp with FROM_UNIXTIME(). Parameters: 1. The format string. 2+. Any arguments needed by the format string. */ function format($format) { $argument = 0; for ($i = strpos($format, '%'); $i !== false; $i = strpos($format, '%', $i + strlen($replace))) { $value = func_get_arg(++$argument); switch (substr($format, $i + 1, 1)) { case '%': $replace = '%'; --$argument; break; case 's': $replace = $value; break; case 'S': $replace = addslashes($value); break; case 'q': $replace = format('"%S"', $value); break; case 'l': $replace = preg_replace('/([%_\\\'"])/', '\\\\1', $value); break; case 'd': $replace = (int)$value; break; case 'n': $replace = (strlen($value) == 0 ? 'NULL' : format('%q', $value)); break; case 't': $replace = format('FROM_UNIXTIME(%n)', $value); break; default: $replace = ''; } $format = substr_replace($format, $replace, $i, 2); } return $format; } I use it like this: format('SELECT * FROM users WHERE id = %d AND userName = %q AND address LIKE "%%%l%%"', $id, $userName, $city)

Aach, I so carefully made sure to indent it properly and blew it on the final click of "Post." Here's the code properly indented. Sorry for taking up so much space on the page! /* Takes a format string and an list of arguments and substitutes the format    codes in the format string for the elements of the arguments list. Similar to    vsprintf, but specialized for generating SQL queries.        Format codes are denoted by a percent sign (%) followed by another character. To    insert a literal percent character into the output string, escape it (%%). Each    successive format code is replaced by its corresponding argument in the list.            %%: Replaced by a literal percent sign.        %s: The argument, which must be a string, is used verbatim.        %S: Replaced by the argument, which is escaped with addslashes.        %q: Use for quoted strings. Like %S, but the value is also surrounded with quote            marks.        %l: Use for LIKE strings. Like %q, but the _ and % characters are also escaped.        %d: The argument, which should be numeric, is used verbatim. Non-numeric            arguments will be converted into numbers first.        %n: Like %q, but empty strings are replaced with NULL instead of ''.        %t: Like %n, but converts the result to a timestamp with FROM_UNIXTIME().            Parameters:      1.  The format string.      2+. Any arguments needed by the format string. */ function format($format) {     $argument = 0;     for ($i = strpos($format, '%'); $i !== false; $i = strpos($format, '%', $i + strlen($replace)))     {         $value = func_get_arg(++$argument);                  switch (substr($format, $i + 1, 1))         {           case '%': $replace = '%'; --$argument;                                        break;           case 's': $replace = $value;                                                  break;                                                                                        case 'S': $replace = addslashes($value);                                      break;           case 'q': $replace = format('"%S"', $value);                                  break;           case 'l': $replace = preg_replace('/([%_\\\'"])/', '\\\\1', $value);          break;           case 'd': $replace = (int)$value;                                             break;           case 'n': $replace = (strlen($value) == 0 ? 'NULL' : format('%q', $value));   break;           case 't': $replace = format('FROM_UNIXTIME(%n)', $value);                     break;                      default:  $replace = '';         }         $format = substr_replace($format, $replace, $i, 2);     }     return $format; }