Stefany93 Posted October 23, 2015 Share Posted October 23, 2015 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! Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted October 23, 2015 Share Posted October 23, 2015 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). 1 Quote Link to comment Share on other sites More sharing options...
Stefany93 Posted October 24, 2015 Author Share Posted October 24, 2015 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! Quote Link to comment Share on other sites More sharing options...
Stefany93 Posted October 24, 2015 Author Share Posted October 24, 2015 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.