Ferry Boender

Programmer, DevOpper, Open Source enthusiast.


Category: mysql

The Unexpected SQL Injection

Something every PHP developer should be reading: The Unexpected SQL Injection – When Escaping Is Not Enough The conclusions: Write properly quoted SQL: Single quotes around values (string literals and numbers) Backtick quotes around identifiers (databases, tables, columns, aliases) Properly escape the strings and numbers: mysql_real_escape_string() for all values (string literals and numbers) intval() for […]

Very slow subquery due to HAVING

Today, I was quite mystified by a very slow running query. There was a table named ‘bar’ with about 3000 rows. I wanted to list all the rows that had a duplicate value for a certain field (‘foo’), and only those rows. The solution was to build a query that selected the rows where the […]

Vim and PHP: tips

I’ve been using Vim for years now, but there’s still new stuff to learn. Check out this page for the PDF version of the slides of a talk given by Andrei Zmievski on editing PHP with Vim. His configuration files are also available. Here’s my favourite list of tips: Add the following text to your […]

GadflyB5: SQL Relational Database in Python

Gadfly is a SQL Relational Database that supports a large set of the SQL standard. It keeps the database in-memory while operating on it. It’s also completely written in Python (with the use of some Python/C modules). I don’t know why it’s cool, but it is.

PHP MySQL tips

Interesting PHP MySQL tips. Some comments: 2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode. This refers to field name aliasing, not to actual field names. He doesn’t suggest that you actually name your fields like that in the table, but only when you select them: SELECT product.name AS product_name instead of […]

Modeling Chess using SQL

Here’s an interesting use of SQL: SQLChess – A tutorial on thinking in sets.

SQL: Find duplicate rows

Amazingly enough, I’ve never had the need to find duplicate rows in a table before. But in this case, I somehow forgot to add a key to a particular table in a database and, naturally, duplicates where accidentally inserted. Of course you’ll want to put a key on the table, but first you’ll have to […]

MySQL storage engine woes

Are you suffering from mysteriously failing insert queries due to foreign key constraints errors in MySQL? Have you noticed spontaneous table storage engine changes from InnoDB to MyISAM, but can’t figure out why they’re happening? You may be coming under the influence of the MySQL Command Center which, when altering a table via the Edit […]

RSS2MySQL v0.1

For a colleague of mine: RSS2MySQL v0.1. A Small script which reads in an RSS feed, takes the news items and then puts them in a MySQL database.

MySQL SQL syntax Quick Reference added

I added a self-made MySQL SQL syntax Quick Reference to the articles section. It has information, taken from the online MySQL reference manual, on Data definition (CREATE and ALTER TABLE) and Data manipulation (INSERT, UPDATE, DELETE, SELECT) syntaxis. For now the HTML version is pretty crappy. This is because OpenOffice’s HTML export isn’t showing background […]

The text of all posts on this blog, unless specificly mentioned otherwise, are licensed under this license.