Posted in 2013
Renaming database in PostgreSQL
- 04 April 2013
Sometimes you have a system with legacy naming standards, but you really want to switch over to the new standard to keep all the scripting clean without some exceptions no one is going to remember in 12 months. Oracle had the command ALTER DATABASE, but since Oracle 10 you need to take the database offline and do some magic. MySQL got the RENAME DATABASE option with release 5.1.7 and lost the option again with release 5.1.23 as it was eating data.
Luckily PostgreSQL still has the command ALTER DATABASE so let’s rename a database and the owner. Before we start we need the password and then we need to log in as the PostgreSQL superuser postgres or another account with similar privileges. So first we check the database name and owner.
SpamAssassin to blacklist and unblacklist
- 12 February 2013
SpamAssassin has a feature to blacklist and unblacklist certain e-mail addresses. But recently I noticed something interesting that may need some more investigation. I have all addresses for domain example.org blacklisted, but also unblacklisted certain functional addresses as is shown in the example below.
Now I expected that webmaster@example.org was going to be unblacklisted, meaning the mail would have a spam score of both +100 and -100 making it effective 0 again. This modification resulted in a spam score of +100 and makes me worry that unblacklisting will demand that the domain part needs to be specified instead of having a wildcard. This will require some more testing in the near future, but for now, it may affect other installations.
Starting to stop SQL injections, part 2
- 11 February 2013
In a previous posting, I gave an example of how to make database queries safer by using parameter binding and basically stopping SQL injections. The next step is to make the code more readable and maintainable. This doesn’t sound like a priority for secure software development, but readable code is also code that can be verified and maintained by other people. It gives you the edge to debug problems quickly and invites others to supply patches. So let’s take the example where the previous posting ended.
For one or two parameters this may work, but when queries become bigger you need to start counting, and counting beyond three is a bad idea in most cases. Let’s change the question mark with a named variable called ‘:username’ in this example. One could then use the function bindParam() to specify which named variable needs to be replaced and has additional features, but in this example, we use the standard binding during in execute phase.