Jump to content

How to store table and column names?


Stefany93

Recommended Posts

Howdy,

 

I am writing a forum from scratch because I like to play with fire.

 

Moving on, I can't think of the best way to store table and column names in either variables or constants. And where to store them? Maybe in a new config.php file? I thought about it and not one sane idea entered my head. I really don't like to write them each time in my DB wrapper methods since if i change the name of a table or a column, the whole thing will fall apart. Currently i have it set like this:

<?php


class Posts extends DB
{
  
    public function list_topics_based_on_category()
    {
       return $this->select_from_table_condition('posts_id, posts_title, posts_date, author_id', 'posts', 'posts_categories_id = 1');
    }
    public function display_contributor_name_by_id($user_id_array)
    {
        $contributor_name = $this->select_from_table_condition_user_input('username', 'users', 'id = ', $user_id_array);
        return $contributor_name[0]['username'];

    }
    public function count_comments($topic_id)
    {
        return count($this->select_from_table_condition_user_input('comments_id', 'comments', 'comments_topic_id = ', $topic_id));
    }
    public function select_single_topic($topic_id)
    {
        return $this->select_from_table_condition_user_input('*', 'posts', 'posts_id = ', $topic_id)[0];
    }



}

Please help! Thank you very much!

Link to comment
Share on other sites

How often do you really have to change the table or column names? I've worked on a lot of PHP applications where the identifiers were simply hard-coded within the query strings, and I never had a problem with that.

 

I'm generally not sure if your database abstraction approach is a good idea. I mean, how is

$this->select_from_table_condition_user_input('username', 'users', 'id = ', $user_id)

better than

$this->query('SELECT username FROM users WHERE id = :id', ['id' => $user_id]);

?

 

All I see is disadvantages:

  • Your code is harder to read, because it's not plain SQL.
  • It's less secure, because you don't use prepared statements.
  • Even worse: Your code almost provokes SQL injection vulnerabilities, because there's no clear concept for escaping the input. Some arguments are copied verbatim into the query string, others are appearently auto-escaped. This is extremely confusing. Similar approaches have shown a lot of security problems in the past.
  • Your code is less efficient. For example, fetching all records in order to do a count() within PHP is much, much slower compared to a COUNT(*) within the database system itself.
  • As soon as the queries become more complex, your class will be in the way, because you expect a simple, fixed query structure. What if I need to join multiple tables? What if I need a union?

I my opinion, you should either use plain SQL or a professional database abstraction layer like Doctrine. But don't try to invent your own, especially when that's not even your goal (you said you want to implement a forum).

  • Like 1
Link to comment
Share on other sites

Oh, is this what i was doing a DB abstraction layer? I thought it was wrappers, lol.

 

Yes I am aware of the SQL injection risks, but it is currently under development.

 

Thank you I am looking into Doctrine now.

 

I'd prefer abstractions of SQL since otherwise I'd have to write every single SQL query and that would make my life very difficult.

 

Thank you so much!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.