Grow your CSS skills. Land your dream job.

Database learning

  • # June 10, 2013 at 10:45 am

    I’m going to attempt to create a twitter comment plugin for Kirby and was wondering if anyone has a few great resources on learning about databases? Specifically MySQL.

    Im looking to learn about how to store user data from an API, connect to the DB and output that data.

    __
    # June 10, 2013 at 2:21 pm

    [SQL Antipatterns](http://pragprog.com/book/bksqla/sql-antipatterns) is the best SQL book I’ve ever read. [Use the Index, Luke](http://use-the-index-luke.com/) is a good, easy-read resource as well.

    >Im looking to learn about how to store user data from an API, connect to the DB and output that data.

    That actually sounds less like SQL and more like your application (PHP). (There will still be SQL involved, of course: particularly, how to organize the DB.)

    So, what “user data” are you storing? Just the info you need to connect to twitter’s API? Or do you plan to save the tweets you retrieve to your own DB?

    # June 10, 2013 at 4:19 pm

    @traq

    I plan to retrieve the following data from the API:

    + Username
    + User ID
    + Website (if they have one listed)
    + Token
    + Secret

    Of course I’ll also need to grab the comment text from the form.

    I’d also like to add a feature to that form that only allows that user or myself to edit that comment.

    When the comments are published, I would like to be able to delete, hide those comments via buttons/links.

    __
    # June 10, 2013 at 5:58 pm

    so, they’ll be logging in via twitter, and commenting _only_ on your site? or tweeting them too?

    for users, you’ll need something like…

    CREATE TABLE twitter_user(
    id INT UNSIGNED NOT NULL
    ,username VARCHAR(100) NOT NULL
    ,website VARCHAR(100) DEFAULT ”
    ,token VARCHAR(how-big-is-a-token?) NOT NULL
    ,secret VARCHAR(how-big-is-a-secret?) NOT NULL
    ,PRIMARY KEY (id)
    )ENGINE=InnoDB DEFAULT CHARSET=UTF8;

    and for comments…

    CREATE TABLE twitter_comment(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT
    ,author INT UNSIGNED NOT NULL
    ,created INT NOT NULL DEFAULT CURRENT_TIMESTAMP
    ,published BOOL NOT NULL DEFAULT 0 — 0=”hidden”, 1=”shown”
    ,text VARCHAR(280) NOT NULL
    ,PRIMARY KEY (id)
    ,FOREIGN KEY (author) REFERENCES twitter_user(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    )ENGINE=InnoDB DEFAULT CHARSET=UTF8;

    — sample SQL for new comment
    INSERT INTO twitter_comment (author,published,text)
    VALUES( 12345,1,’This is a comment!’ );
    — everything else is done automatically

    …might need something else, but I can’t think of it right now.

    `text` is the contents of the comment. You can make it longer or shorter as desired. I would make it bigger than the actual size you want to allow (to accommodate multibyte characters). Twice the allowed comment size should be enough in most cases; for languages with non-latin alphabets you might need more. You’ll *almost* definitely never need more than 3x (*definitely* definitely never more than 4x).

    You can order the comments by their timestamp. You can also use it to display the authoring date/time.

    You can restrict editing by checking the id of the logged-in user and matching it to the `author` field. For yourself, you can also compare the id to your own id (ignoring the `author` id).

    You can show/hide the comment by toggling the `published` field.

    If you want to be able to assign comments to different areas/articles/etc., you’ll need an intersection table for that. If you want to be able to thread comments (i.e., comment on comments), you’ll need another intersection table.

    The `FOREIGN KEY` makes sure that every comment `author` matches an `id` in the `twitter_user` table – if a user doesn’t exist yet, you’ll get an error (and can then do something useful, like ask them to log in with twitter).

    `ON UPDATE CASCADE` means that, if a twitter_user id ever changes, the `author` field will be updated with the correct value.

    `ON DELETE CASCADE` means that, if the twitter_user is ever deleted, the comment will be deleted too. You could also do `RESTRICT` (the user *can’t* be deleted if they still have comments) or `SET NULL` (the user can be deleted; their comments will become orphans). I think `CASCADE` is the best choice here.

    # June 11, 2013 at 1:04 am

    @traq Wow. Thank you!

    > so, they’ll be logging in via twitter, and commenting only on your site? or tweeting them too?

    Strictly commenting. I will not be tweeting anything from their profile. Therefore, I should only need read permissions.

    What about if a user adds malicious javascript to the form? Isn’t there a way to prevent that from happening?

    I see you wrote VARCHARS (100). I assume this means it will only allow up to 100 characters. What about if usernames are restricted to 15 by Twitter? Should I just set it at 15? I’m also not sure that I want to restrict comments to a specific character count. I’m not even sure what is reasonable or if that’s a bad decision. I’ve seen some comments from around the web, as we all have. Some are 7 characters long and others exceed over 1500.

    > If you want to be able to assign comments to different areas/articles/etc., you’ll need an intersection table for that.

    Oh, no. I completely forgot about this. Somehow I need to get an article’s unique ID so it knows which comments to post on each article. I don’t think Kirby does this.

    > The FOREIGN KEY makes sure that every comment author matches an id in the twitter_user table – if a user doesn’t exist yet, you’ll get an error (and can then do something useful, like ask them to log in with twitter).

    Would I need this if I plan to not show the form unless they’re signed in?

    # June 11, 2013 at 1:27 am

    > I see you wrote VARCHARS (100). I assume this means it will only allow up to 100 characters.

    I believe VARCHAR would truncate remaining empty space. So no need to worry about it. Just make sure it doesn’t exceed 100 char.

    For string `123_ ` space denoted by `_` VARCHAR(4) would be `123` no empty space.

    __
    # June 11, 2013 at 1:30 am

    > What about if a user adds malicious javascript to the form? Isn’t there a way to prevent that from happening?

    Do you plan to allow _any_ html? If not, then it’s simple: use [`strip_tags`](http://php.net/strip_tags). If you want to allow html _as text_ (e.g., posting code for others to read), then you’ll need to save the comment as-is and convert the html into its entity equivalents (e.g., using [`htmlspecialchars’](http://php.net/htmlspecialchars)) **when you display it**.

    There’s also things like [HTMLPurifier](http://htmlpurifier.org). You should check what Kirby does natively to handle user-submitted text.

    > Somehow I need to get an article’s unique ID so it knows which comments to post on each article. I don’t think Kirby does this.

    I’d be surprised if it didn’t. But if it doesn’t, you’ll just have to figure out how it gets those ids internally, and you can re-create it (hey, two plugins for the price of one!).

    > I see you wrote VARCHARS (100) *[…]* usernames are restricted to 15 by Twitter? Should I just set it at 15?

    Up to you. twitter (currently) only supports ASCII characters in usernames, so you don’t need extra space.

    > Would I need this if I plan to not show the form unless they’re signed in?

    It’s very useful anyway:

    1) The foreign key allows MySQL to keep track of the relationship between the two tables on its own. This means (a) you don’t have to, and (b) you will _never_ mess it up by accident.

    2) More generally, you should **never** rely on a form not being shown “unless they’re signed in” – registering just for the purpose of getting a copy of the form to launch an attack later is simple and very common.

    Besides, unless this is a personal project that you don’t ever intend to share, the details of your form will be freely available anyway.

    # June 11, 2013 at 2:16 am

    Thanks @AlenAbdula

    @traq

    > Do you plan to allow any html? If not, then it’s simple: use strip_tags. If you want to allow html as text (e.g., posting code for others to read), then you’ll need to save the comment as-is and convert the html into its entity equivalents (e.g., using `htmlspecialchars’ when you display it.

    I do. So using `htmlspecialchars` would be the solution.

    > Up to you. twitter (currently) only supports ASCII characters in usernames, so you don’t need extra space.

    I’m just trying to reduce the time it takes the database to remove the extra characters. Although, it probably is so minimal I wouldn’t even notice.

    > Besides, unless this is a personal project that you don’t ever intend to share, the details of your form will be freely available anyway.

    My goal is to actually share this for other Kirby users to implement. However, I’m on the fence about this. I really don’t want to be responsible for a wide-scale attack on Kirby sites if I fuck something up.

    # June 11, 2013 at 2:27 am

    Ooh. I just saw this post: http://getkirby.com/blog/database

    # June 11, 2013 at 4:52 am

    > I’m just trying to reduce the time it takes the database to remove the extra characters.

    See first table here http://dev.mysql.com/doc/refman/5.0/en/char.html, no extra characters are stored unlike the CHAR type.

    # June 11, 2013 at 5:10 am

    Thanks @CrocoDillon

    __
    # June 12, 2013 at 2:22 am

    >I’m just trying to reduce the time it takes the database to remove the extra characters. Although, it probably is so minimal I wouldn’t even notice.

    As Crocodillon said, the extra spaces don’t get saved (that’s the diff between VARCHAR and CHAR). However, the DB still has to allow for however many characters you specify, so specifying 100 when you only need 15 will (pointlessly) allocate more memory for the column and slow things down when you’re reading the table. Not very much, of course. You would absolutely never notice it. Using TEXT to store 15 characters, on the other hand, … : )

    >My goal is to actually share this for other Kirby users to implement. However, I’m on the fence about this. I really don’t want to be responsible for a wide-scale attack on Kirby sites if I fuck something up.

    test,test,test;share,share,share

    You can create/adopt coding conventions to keep track of what is safe to print and what’s not. For example,

    < ?php
    $unsafe_name = $_POST;
    // print $unsafe_name; // NOOOOOOOOOOO!!!!!!
    $safe_name = htmlspecialchars( $unsafe_name );
    print $safe_name; // yes : )

    Joel Spolsky [explains it better](http://www.joelonsoftware.com/articles/Wrong.html).

    # June 12, 2013 at 2:40 am

    @traq

    > Using TEXT to store 15 characters, on the other hand, … : )

    This is flying over my head, mind elaborating?

    After further thought, I’m on the fence leaning towards not releasing this as a “plugin”. The reason being is because I’m sure many people will be sending bug reports and unfortunately I won’t be able to help them as it’s not my area of expertise. And with school, it will pretty much be impossible to keep updating it. But I don’t mind writing an article on how I went about it along with credits to those of you who’ve helped me along the way.

    __
    # June 12, 2013 at 2:51 am

    >This is flying over my head, mind elaborating?

    TEXT is a really big datatype (63,000-some-odd characters). In fact, it’s stored separately from the table itself, so accessing it to read a single small word would be very, very inefficient.

    I’m just making an absurd example.

    >After further thought, I’m on the fence leaning towards not releasing this as a “plugin”. The reason being is because I’m sure many people will be sending bug reports and unfortunately I won’t be able to help them as it’s not my area of expertise. And with school, it will pretty much be impossible to keep updating it. But I don’t mind writing an article on how I went about it along with credits to those of you who’ve helped me along the way.

    Whether you release it “officially” or just make it available to “use at your own risk,” I’d say go ahead and do it! It’ll be fun, and a learning experience.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".