How to store table and column names?

[Stefany93]

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!

[Jacques1]

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).

[Stefany93]

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!

[Stefany93]

Doctrine looks really cool! OMG I got very excited.

 

I didn't know you weren't suposed to write your own DB abstraction layers, I thought that was a rule only for the encryption, datetime, hashing, and a few others.