Try out my first PHP web app!

[__]

Do you mean “bigger” by more work or more users?

Definitely “more work.” If you’re lucky (and good), “more users.”

there are over 50 new users signed up on there?

Two of them are mine. But I have noticed spam messages on your “forum.” Weed, penny stocks, someone’s blog, various nonsense comments and XSS/SQL injection attempts. When you have an unchallenged, unverified registration system, you’ll get a lot of members who are there for the sole purpose of spamming (automated or otherwise).

On the plus side, this means that you’re getting traffic… so it’s a success in that sense.

[chrisburton]

XSS/SQL injection attempts

The JS was me. Just checking his security.

[__]

The JS was me. Just checking his security.

hehe, yeah, I did one too. Also tried basic SQL injection on the login form.

[chrisburton]

I have people registering under the same username as other people and gaining access to their account

That’s a real problem. How are you setting up users? There should be a unique identifier for them. Why are you not checking if a user exists? That should determine whether the data is stored on the database or if they need to choose another username as it has already been taken.

Another question, do you think I should be using seperate databases for my registered users, the forums, etc?

I don’t see why you would need multiple databases off the top of my head. You should however separate the users from the forum data into two tables.

[__]

Any suggestions?

Without knowing what you did in the first place, no. As I mentioned earlier, it should not be possible for these sorts of things to happen: unique identifiers should be unique, and the database should reject duplicates.

Did the SQL injection attack that you attempted work?

No. But I didn’t persist; I just tried a few common, superficial attacks.

do you think I should be using seperate databases for my registered users, the forums, etc?

No… in fact, that would probably be counter-productive.

…I will admit I think it is a little un-organized.

That isn’t good. How is it organized now? what does you schema look like?

[chrisburton]

@un-traq-ed

It’s @traq.

So you’re not checking to see if a user exists? What if a user created a duplicate of an administrator account?

[Alen]

Well, whenever someone registers a new user account. A new table is added to the database and the table is named whatever the username is.

No! Do not do this!

You need to create a pivot table, or assign foreign key to corresponding tables. So you’ll have something like this:

[__]

A new table is added to the database

Right — if you mean what you actually said, no! do not do this!

You should create your database schema exactly once. No site functionality should require creating new tables in the database. Follow Alen’s example of using foreign keys to show relationships between data: that’s exactly what they’re for.

Further, if you want the usernames to be unique, checking from within your PHP scripts is the wrong approach. For example:

1. New user A asks if the username “Clancy” is available. PHP checks, and it is.
2. New user B asks if the username “Clancy” is available. User A hasn’t actually registered yet, so when PHP checks, the username is still available.
3. User A finishes registering, and now has the username “Clancy.”
4. User B finishes registering, and now also has the username “Clancy.”

This is called a race condition. It’s not PHP’s job to prevent this sort of thing;* it’s the database’s job. username should be a primary key. This way, user A would get the name Clancy, and user B would get an error on insert.

* now, if you want to use PHP to check username availability for convenience —so you don’t waste the user’s time on names you know are already taken— that’s great. That’s entirely appropriate.

It’s @ traq.

hehe, I actually changed it so I wouldn’t get as many @ mentions in my inbox.

Writing it out now It actually seems pretty organized.

If you’d like further database advice, please share your table schemas (use show create {table-name-goes-here}).

[__]

It looks like the photo of the DB example you showed is what I have for my comments on the forum section

Alen’s example was just to demonstrate how to use foreign keys to associate (in this case) comments with the users they belong to. It’s not a complete design for either table.

[__]

whenever someone new registers they get a user_id that is one greater then the person who registered before.

This is a fairly common approach, but it is not the most straightforward: the reason for having an auto-incrementing ID is to uniquely identify something that is not naturally identifiable. A user is, however: the username must be unique across all users, so it’s a perfect choice for a “natural” primary key. The user’s email address is another natural choice (if you don’t use the email as the PK, it still must be unique).

create table user(
    username varchar(100) primary key,
    email varchar(100) not null,
    -- etc., etc...
    unique key(email)
)engine=innodb;

Comments might look like so (note we do use an auto-incrementing id here, since there is no “natural” value to be the primary key):

create table comments(
    id serial primary key,
    author varchar(100) not null,
    -- etc., etc…
    foreign key(author) references user(username)
)engine=innodb;

edit
I am suddenly having huge problems with markdown interpreting mid-code backticks as code delimiters, so I am leaving them out. Note you should always use backticks to delimit identifiers (like table or column names). In my code above, for example, the table name user would be confused with the MySQL command and cause a syntax error.

[chrisburton]

It looks like the photo of the DB example you showed is what I have for my comments on the forum section. Each forum has a specific Id and the comments are added to the database with that specific id of the forum post the were posted on. When the post is clicked. Php pulls all comments where the ID of the post = the Id of the comment.

That’s how you’re distinguishing which comments belong on certain posts. That’s not what Alen is suggesting. Take a look at the users ID on the first table and then look at the second table’s user_id. See how those ID’s match?

[__]

Might you for some reason feel the need to change someone’s name (example: he requests it, or it’s an offensive name), this should not break any of the references in any code as I’d be using the ID.

Ahh, but if you use primary keys/ foreign keys, then changes are simple: use cascade, and when you update the username, all references to it will be updated automatically also. This makes the whole database stronger, by allowing it to handle referential integrity directly (as it was designed to).

I’m not saying you can’t use a serial PK if you want to. That’s okay. But in this case, it is a “workaround” for a problem that doesn’t exist.

[__]

when someone types in a username that they want to send the message to, how can I take that username, get the userID, and insert it into the table with the userID it belongs to?

You’d need to do something like:

insert into comment(author, etc) values(
    (select id from user where username=?),
    'etc.'
);

Further, if author is a foreign key, you’d be sure you will never have a comment attributed to a user that doesn’t exist.

Maybe a while loop?

Try to avoid doing things in your application code that could be done in the database. Further, putting SQL queries inside a loop should be a big red flag: it’s too easy for such operations to run out-of-control.

[__]

Ya know, as a relative URL, codeyourweb.net resolves to https://css-tricks.com/forums/topic/try-out-my-first-php-web-app/page/4/codeyourweb.net.

[__]

Yes. You probably still have time to [edit] and fix the link URL in your post above; it’s been less than a half hour.

[Author]

Posts