Roberto Perez wrote:
> At 12:38 PM 11/11/04, Ian Skinner wrote:
>
>>I don't know how unique the url's are in your data, but if any of them
>>are repeated you would probably want an "URL" table.
>
> Actually, that is correct. Some URL's may be good for more than 1 word. I
> was just thinking of updating them manually one by one, but if there's a
> better way, I'm all ears :-)
I'd say this is somewhere where the redundancy is harmless, and where
going with just two tables would be just fine. However, I don't know the
exact mechanics of your app, so I can't tell. But let's continue
regardless...
>>And your select query would become something like this:
>>SELECT
>> WORD, URL
>>FROM
>> WORD_TBL, URL_TBL,W ORD_URL_JOIN
>>WHERE
>> URL_TBL.ID = WORD_URL_JOIN.URL_ID
>>AND WORD_URL_JOIN.WORD_ID = WORD_TBL.ID
>>AND WORD_TBL.ID = <cfqueryparam value="#myWordVariable#"
>>CFSQLTYPE="SQL_DB_CHAR">
>
> I'm not familiar with joins, so these questions may sound silly, but here
> they are:
You probably know more than you realise.
> - in the last line, shouldn't I have "WORD_TBL.WORD" instead of
> "WORD_TBL.ID"? Because the user will type a word, that should match the
> actual word stored under that record ID.
You're right.
> - I understand the use of #myWordVariable#, which would come from the text
> entry field in the form used to search the dictionary. But, what would
> "cfqueryparam" and "cfsqltype" do? In other words, are they required, or
> just "good-to-have"?
You should take a read of what it says on LiveDocs about CFQUERYPARAM.
Its use isn't required, but it's a really good idea if you don't want to
end up with serious amounts of egg on your face.
The naive way of passing values into queries is just to dump them in
verbatim, as follows:
SELECT user_id
FROM users
WHERE user_name = '#FORM.username#'
AND user_password = MD5('#FORM.password#')
But there are serious problems with this. Firstly, it's harder for the
DBMS to identify common patterns in the queries if you do this as it
might not be able to identify common execution plans. If the DBMS does
execution plan caching, building queries like this can damage potential
performance. But YMMV, and this dependant on the internal mechanics of
the DBMS as to whether it yields an improvement or not, but it rarely
if ever degrades performance.
Secondly, and more importantly, is the risk of SQL injection. Say
somebody enters the following text into the "username" field of the
form:
blah';UPDATE users SET user_password = MD5('cracked') WHERE
user_name = 'admin'; SELECT user_id FROM users WHERE user_name =
'admin
which will change the admin password to 'cracked'. Or even subtler:
admin';--
which will (depending on how the DBMS handles SQL errors) log the user
in directly as the 'admin' user.
Frightened? You should be. This is one of the most common website
exploits out there. CFQUERYPARAM was specifically added because of this
exploit.
CFQUERYPARAM--I'm simplifying a bit here--ensures that any special
characters are escaped so that SQL injections are defeated. It also
validates that type of the data passed in in different ways such as
whether it's numeric or a string, whether it's short enough to fit in
the field (for text--memos and (var)char--fields: you need to specify
a maximum length in the tag), and a host of other things.
The above query, secured using CFQUERYPARAM would read something like:
SELECT user_id
FROM users
WHERE user_name = <cfqueryparam value="#FORM.username#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="32">
AND user_password = MD5(<cfqueryparam value="#FORM.password#"
cfsqltype="CF_SQL_VARCHAR"
maxlength="32">)
The "cfsqltype" attributes specifies the type of the value. For
instance, if you wanted to get all the articles by such-and-such an
author, and you have the id that's associated with them, you'd do
something like the following:
SELECT article_id, title, body, date_posted
FROM articles
WHERE author_id = <cfqueryparam value="#URL.author_id#"
cfsqltype="CF_SQL_INTEGER">
If you ended up passing in "fred" as the value of "URL.author_id",
CFQUERYPARAM would catch this and throw an error (which is what you
want).
Really, read up on it and start using it.
> - any reason why, in the "WHERE" statements, you used "URL_TBL.ID" first
> (i.e., before the "=" sign), but then you used a similar expression
> ("WORD_TBL.ID") second (i.e., after the "=" sign) in the next part?
I'm going to rewrite Ian's code because I find using "id" as the name of
primary keys confusing myself. The code is for MySQL, but should work
just fine on Access. Here's the schema:
CREATE TABLE words
(
word_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(64) NOT NULL,
-- Because you'll be doing lookups on "word", and it must be unique.
-- Relational purists would say that "word" itself should be the
-- key, but I don't think anybody on this list fits into that
-- category. ;-)
UNIQUE (word)
);
CREATE TABLE links
(
link_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
link VARCHAR(255) NOT NULL
);
-- I'm not putting a primary key on this table because it doesn't need
-- one. Seriously. This is a link table, after all, so if there's any
-- primary key, it's word_id and link_id together. link_id isn't indexed
-- because I doubt there's ever going to be a time you'll be doing
-- lookups for all the words associated with a particular link.
CREATE TABLE word_links
(
word_id INTEGER NOT NULL,
link_id INTEGER NOT NULL
UNIQUE (word_id, link_id),
-- For when you're looking up links from a word. Essential.
INDEX (word_id)
);
With this schema, the lookup query is:
SELECT link
FROM words AS w
JOIN word_links AS wl ON w.word_id = wl.word_id
JOIN links AS l ON wl.link_id = l.link_id
WHERE w.word = <cfqueryparam value="#FORM.word#"
maxlength="32" cfsqltype="CF_SQL_VARCHAR">
This looks up the word in the "words" table, getting its id. Then
it joins to the "word_links" table to get the ids of all the links
associated with that word through the "word_links" table. Finally,
it joins to the "links" table on the link ids found, giving the
links.
End of lesson. :-)
K.
--
Keith Gaughan, Developer
Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland
http://digital-crew.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184129
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
