Tips (25)


Replacing Substrings using SQL, not PHP

Let’s say you have a problem: A recent DB migration has escaped a lot of quote marks within your fields and now your app is acting funny. Your task is to fix the problem, and do it FAST.

Enter the replace() function in MySQL (assumed other RDBMs as well, such as Postgres and MSSQL)
UPDATE table_name SET table_field = replace(table_field, ‘replace_that’, ‘with_this’);

I was seeing a lot of ” text in some forums output, so I was on a mission to make nice with the forums administrator:
update posts_text set post_text = replace(post_text,'\"', ‘'"'’);
Double backslash? Yes. Quotes are OK, but the backslash is an escape character, so to actually print the backslash, you need to escape the escape character. You’d do the same if you wanted n to not be treated as a newline return: \n




What’s your Fortune?

Here’s a fun little thing you can do for your next site goodie: display your users’ “fortunes” from the command-line interface, CLI, application Fortune.

On Fedora, install:
% sudo yum install fortune-mod

from PHP (fortune.php)
<pre>
<? passthru('fortune'); ?>
</pre>

I pre-formatted the text because it comes out looking as it does in the CLI. In its simplest form you can simply include this into a nice little div tag somewhere on your site (little side box above/below the nav?). If you don’t have privileges to install apps on your machine, this probably won’t work for you.

While you’re at it, take a look at simply parsing RSS feeds for your site. I found a good source over at BrainyQuote.com. Give it a shot!




Fixing Zen Cart’s Tax Miscalculation When Using a Coupon

I was recently alerted to an error in Zen Cart by a client where the tax was being miscalculated when using a coupon. Here’s the fix.

Around line 240 in /store/includes/modules/order_total/ot_coupon.php

Remove these lines:

//$od_amount[$tax_desc] += (($products[$j]['final_price'] * $products[$j]['quantity']) * $tax_rate)/100 * $ratio;
//$od_amount[$tax_desc] += round(((($products[$j]['final_price'] * $products[$j]['quantity']) * $tax_rate) + .5)/100 * $ratio, 2);

Replace with this:

//hack by Cameron P. to fix the coupon tax errors.
//for some reason the tax was off by the number of products in the cart times the tax (not quantity)
$od_amount[$tax_desc] += round(((($products[$j]['final_price'] * $products[$j]['quantity']) * $tax_rate) )/(100*sizeof($products)) * $ratio, 3);

I did a few things here:
* The tax was off by the tax total times the number of products in the cart (independent of quantity), so that’s why the sizeof() bit is in there
* Removed the + 0.5 addition to the total. Why that’s even in there to begin with, I don’t know. It was making some of my totals come out wrong by a few cents here and there. The new equation seems to work better without it.
* Extended the price to three significant figures. Probably not 100% necessary, but it seemed to help fix some of the issues where the total was off by a penny or two.

    Hope this helps!




    Clean file names using PHP preg_replace

    It’s always a good idea to protect yourself from all sorts of possible malicious attempts by users (or even mistakes by misinformed users). Here we look at taking a string of text (a filename) containing characters that are generally speaking unsafe.

    Here’s a simple way to clean-up filenames (or other text input) using PHP – leaving only alphanumeric characters, dashes, underscores, and periods. I’m not great with regular expressions, but it seems one should be able to use preg_replace() to replace every character that’s *not* within a defined range… but that’s not really the case

    I don’t want to assume too much, but it seems like /(![[:alnum:]_.-]*)/ should match all the baddies in the string. It doesn’t. The solution, rather, is to find all the baddies by replacing all the OK characters into a temporary variable that can be used to strip them from your string.


    $fname="Filename 123;".'"'."lal[a]*(/.jpg"; //yikes!
    $replace=""; //what you want to replace the bad characters with
    $pattern="/([[:alnum:]_.-]*)/"; //basically all the filename-safe characters
    $bad_chars=preg_replace($pattern,$replace,$fname); //leaves only the "bad" characters
    $bad_arr=str_split($bad_chars); //split them up into an array for the str_replace() func.
    $fname=str_replace($bad_arr,$replace,$fname); replace all instances of the bad chars with the replacement
    echo $fname; //just echo the name for your satisfaction

    Or just simply

    $fname="Filename 123;".'"'."lal[a]*(/.jpg";
    $replace="_";
    $pattern="/([[:alnum:]_.-]*)/";
    $fname=str_replace(str_split(preg_replace($pattern,$replace,$fname)),$replace,$fname);

    Conclusion:
    Though it might not seem like a big deal to replace spaces and the like with underscores, consider the possibility of a user injecting code and commands, that when the string is used in the right context, can compromise your site and its data:


    $fname="' OR super_top_secret=1;";
    $result=mysql_query("SELECT * FROM files where fname='$fname' LIMIT 1");

    And with that a malicious filename allows all of our top secret files to be visible when it should have only been just one. Granted, we should escape anything that goes into the DB query, but as far as I know, it is possible to upload a file with that exact name (or change the name if the online app allows it). So for now, we’ll just restrict it to only characters that play nice with the web server.




    Cool Exposé trick in Mac OS X

    When pressing F10 (or Ctrl-F10) to show a program’s open windows, you can press Cmd-Tab to cycle through all running apps, showing the icons across the screen as the normal Cmd-Tab does. You can also scroll through running apps in Exposé by pressing Cmd-` (back tick), but without the icons. Apparently this will also switch you from all windows Exposé to only one app at a time (the F10 effect).