SQL Query from plugin
First off, I know it's not recommended for a plugin to deal directly with the database. Bear with me.
I am writing a plugin that creates clean URLs for pages - for example:
...redirects to...
myelggsite.com/pg/pages/view/16
I have created a table in the Elgg database named 'elggredirection'. This table holds the clean url text (in this case, 'mypage'), and the associated guid (16). When someone visits the clean URL, Apache redirects them to a PHP file which looks up the URL in the database, finds the GUID, and redirects them to the appropriate page.
When a page is created, my plugin displays an additional text box for the clean URL, which is then written as as piece of metadata ($page->cleanurl). I then want to write this to my table along with the GUID.
So, my question - can I do this using Elgg's built-in query methods in the engine, rather than PHP's MySQL functions? Here's my code so far:
$escapedURL = sanitise_string($page->cleanurl);
$link = $CONFIG->dblink;
$id = $page->guid;
$query = sprintf("INSERT INTO elggredirection (subdomain, guid) VALUES (%s, %s)",$escapedURL, $id);
execute_query($query, $link);
This doesn't seem to work, I assume because I'm not getting the DB link correctly. Any suggestions (apart from 'this is a bad idea' - it may well be, but I'm just interested as to how I could do this using Elgg).
- Brett@brett.profitt
Brett - 0 likes
- lxt05@lxt05
lxt05 - 0 likes
- Brett@brett.profitt
Brett - 0 likes
- mahmoudimus@mabdelkader
mahmoudimus - 0 likes
- Brett@brett.profitt
Brett - 0 likes
- lxt05@lxt05
lxt05 - 0 likes
You must log in to post replies.Could be a very useful plugin!
The problem is in your query--You're not escaping the URL. You're sorta using prepared statements, but in this case they're not much use because you're simply substituting $id and $escapedURL with %s by sprintf.
You'll be better saying:
$escapedURL = mysql_real_escape_string($page->cleanurl);
$id = mysql_real_escape_string($page->getGUID());
$query = "INSERT INTO elggredirection (subdomain, guid) VALUES ('$escapedURL', $id)";
execute_query($query);
Thanks very much - I'll try this tomorrow and let you know how I get on.
Sorry--The problem isn't that you aren't escaping the URL; it's that you aren't quoting the URL.
I agree, I see some extremely great uses for this.
A typo for Brett's query above:
$query = "INSERT INTO elggredirection (subdomain, guid) VALUES ('$escapedURL', '$id')";
Also, I would use insert_data() insert of execute_query()
Ints don't need to be quoted for MySQL ;) But it is probably good practice...
insert_data() is the better function to use! Good catch on that one.
Thanks very much everyone - just to update, execute_query() didn't seem to work at all, whereas insert_data() worked fine, so the code ended up like:
// Save the clean URL to the database with the GUID
$escapedURL = mysql_real_escape_string($page->cleanurl);
$id = mysql_real_escape_string($page->getGUID());
$query = "INSERT INTO elggredirection (subdomain, guid) VALUES ('$escapedURL', '$id')";
insert_data($query);
Now I've just got to make sure when a page is deleted the database entry is removed, which should be as simple as using delete_data(). I'll need to tidy it up before I release it as a plugin (currently I'm putting together an extranet using Elgg), so once I'm done with that I'll post it.