Versio

Mijn Database Class

Webdesign & development » Algemene PHP scripting en meer » Mijn Database Class

Overzicht Reageren

Johan Kruse

Johan Kruse

15/06/2009 23:21:00
Quote Anchor link
Ik heb al een tijdje geen internet meer, en zit nou een kleine studie te doen in ebooks. Allemaal heel leuk en aardig, maar sommige boeken zijn niet meer zo recent.

Nu heb ik een database class gemaakt, het werkt precies zo als ik wou, maar ik wilde even wat commentaar erop krijgen, wat verbetered zou kunnen worden, wat slecht is, etc, etc. Dit soort dingen vind je niet echt in boeken, dus daarom maar even hier.

Ik ben bezig een spel aan het schrijven, en deze code is er een onderdeel van.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
<?php
    
    /*
        #    Author:         Johan Krüse.
        #    E-mail:        Johan.Kruse@gmail.com.
        #    Date:            Tuesday, June 09, 2009.
        #    Type:            Class.
        #    Version        1.0
        #    PHP                5.2.8
        #    Licence:        Do whatever you please with this class, but just keep the original credits in here.
    
        The database class enables you to easily swap / execute querys between different mysql users, which is usefull
         because giving a person full access to the database while you only need to extract data from the database is a security risk.
        This class only executes and connects to the database when it is actually required, thus saving some executing time.
        
        To deal with escaping characters super globals ( post, get, cookie ) are also stored in this class, auto escaped and ready to be used.
        Depending on your magic quote gpc setting, these values are either escaped or stripped, confusing? No.
        If magic quotes is turned on, the real super globals hold the escaped values and this class the stripped, and so the other way around.
        There are more things that this class does, here is a quick list.
        
            * Mysql login details are only accessible from inside the class.
            * Object orientated error handling.
            * Able to store results in this class.
            * Auto connect to the database.
            * Auto select the database ( only if it is registered with the user ).
            * Auto escaping / stripping strings.
        
        The public variables are importand in order to use this class, they are listed below this paragraph in 3 groups.        
        Group 1 exists of "last" variables which hold values of the last executed query, also the last mysql error.
        This is not thrown in the exception because of security reasons, we don't want the user to know what's goin on in the server!
        Group 2 are array's which stores the last query data into the array using squery.
        The last group are the copys of super globals either escaped or stripped.
        
            * $lastSQL / $lastResult / $lastError
            * $SQL / $result
            * $post / $get / $cookie
        
        In the methods there is usually an optional variable called key, if this argument is not given the default user is assumed.
        The default user is the last created database user or the user which is selected by the method user_switch.
        Keep in mind that the default user ( passing no argument ) is the fastest execution time.
            
            * user()
            * user_switch( $key )
            * user_create( $key, $hostname, $username, $password[, $database] )
            * connected( [ $key ] )
            * selected( [ $key ] )
            * connect( [ $key ] )
            * disconnect( [ $key ] )
            * select( [ $db[, $key ] ] )
            * query( $sql[, $key ] )
            * sQuery( $name, $sql[, $key ] )
            * escape( $array[, $escape ] )
        
    */
    
    
    class database
    {
        const         HOSTNAME    = 0x10;
        const         USERNAME    = 0x11;
        const         PASSWORD    = 0x12;
        const        DATABASE        = 0x13;
        const        HANDLE            = 0x14;        
        const        CONNECTED    = 0x15;
        const      SELECTED        = 0x16;
        const         DATABASES    = 0x17;
        const        LINK                = 0x18;
        
        private     $pData            = array();
        private    $pKey                = null;
        private    $pEscape        = null;
        
        public    $SQL                = array();
        public    $result            = array();
        public    $lastSQL        = null;
        public    $lastResult        = null;
        public    $lastError        = null;
        
        public    $post                = array();
        public    $get                = array();
        public    $cookie            = array();
        
    #    This function returns the default user if the key is not a valid user.
        private function key( $key )
        {
            switch( $key )
            {
                case null:
                    return $this->pKey;
                case $this->user_exists( $key ):
                    return $key;
                default:
                    throw new exception( 'Account "' . $key . '" does not exist.' );
            }
        }
        
    #    Returns the currenct default user name.
        public function user()
        {
            return $this->pKey;
        }
        
    #    Switch to a new default user.
        public function user_switch( $key )
        {
            return $this->user_exists( $key ) ? $this->pKey = $key : false;
        }
        
    #    A check if a user exists within this class.
        public function user_exists( $key = null )
        {
            return isset( $this->pData[$key] );
        }
    
    #    Create a user for this class.
        public function user_create( $key, $hostname, $username, $password, $database = null )
        {
            if( ! $this->user_exists( $key ) )
            {
                $this->pKey                 = $key;
                $this->pData[$key]     = array
                (
                    database::HOSTNAME     => $hostname,
                    database::USERNAME     => $username,
                    database::PASSWORD     => $password,
                    database::DATABASE     => $database,
                );
            }
            else
            {
                throw new exception( 'Could not create a database account. Username "' . $key . '" is already registered.' );
            }
        }
        
        #    Are we connected to the database with a user?
        public function connected( $key = null )
        {
            return isset( $this->pData[$this->key( $key )][database::CONNECTED] ) && $this->pData[$this->key( $key )][database::CONNECTED]  ? true : false;
        }
        
        # Did the user already have a selected database?
        public function selected( $key = null )
        {
            return isset( $this->pData[$this->key( $key )][database::SELECTED] ) && $this->pData[$this->key( $key )][database::SELECTED] ? true : false;
        }
        
        # Establish a new connecting to the database.
        public function connect( $key = null )
        {
            $key = $this->key( $key );
        
            if( ! $this->connected( $key ) )
            {
                if( $this->pData[$key][database::HANDLE] = @mysql_pconnect( $this->pData[$key][database::HOSTNAME], $this->pData[$key][database::USERNAME], $this->pData[$key][database::PASSWORD] ) )
                {
                    $this->pData[$key][database::CONNECTED] = true;
                }
                else
                {
                    $this->lastError = mysql_error();
                    
                    throw new exception( 'Could not connect to database with user "' . $key . '". ' );
                }
            }
            else
            {
                throw new exception( 'Refused to connect to the database, the handle for user "' . $key . '" is already in use.' );
            }
        }
        
        # Disconnect from the database.
        public function disconnect( $key = null )
        {
            $key = $this->key( $key );
            
            if( $this->connected( $key ) )
            {
                if( mysql_close( $this->pData[$key][database::HANDLE] ) )
                {
                    $this->pData[$key][database::CONNECTED] = false;
                }
                else
                {
                    $this->lastError = mysql_error();
                    
                    throw new exception( 'Could not disconnect from the database with user "' . $key . '".' );
                }
            }
            else
            {
                throw new exception( 'Refused to disconnect from the database, the handle for user "' . $key . '" is not initialized.' );
            }
        }
        
        #    Select a database for a user.
        public function select( $db = null, $key = null )
        {
            $key     = $this->key( $key );
            $db        = isset( $db ) ? $db : $this->pData[$key][database::DATABASE];
            
            if( $this->connected( $key ) )
            {
                $list = mysql_list_dbs( $this->pData[$key][database::HANDLE] );
                
                while( $row = mysql_fetch_object( $list ) )
                {
                    $this->pData[$key][database::DATABASES][$row->Database] = $row->Database;
                }
                
                if( sizeof( $this->pData[$key][database::DATABASES] ) > 0 )
                {
                    if( array_key_exists( $db, $this->pData[$key][database::DATABASES] ) )
                    {
                        if( $this->pData[$key][database::LINK] = mysql_select_db( $this->pData[$key][database::DATABASES][$db], $this->pData[$key][database::HANDLE] ) )
                        {
                            $this->pData[$key][database::SELECTED] = true;
                        }
                        else
                        {
                            throw new exception( 'Could not select database "' . $db . '" on "' . $key . '".' );
                        }
                    }
                    else
                    {
                        throw new exception( 'Database "' . $db. '" does not exist on "' . $key . '".' );
                    }
                }
                else
                {
                    throw new exception( 'There are no databases to be selected on "' . $key . '".' );
                }
            }
            else
            {
                try
                {
                    $this->connect( $key );
                }
                catch( exception $e )
                {
                    throw new exception( 'Selected the database before connecting and failed to establish a connection. ' . $e->getMessage() );
                }
            }
        }
        
        # What shall we query the database for today?
        public function query( $sql, $key = null )
        {
            $key = $this->key( $key );
            
            if( $this->connected( $key ) )
            {
                if( $this->selected( $key ) )
                {
                    if( $this->lastResult = mysql_query( $this->lastSQL = $this->pEscape ? stripslashes( $sql ) : $sql ) )
                    {
                        $this->lastError = null;
                        
                        return $this->lastResult;
                    }
                    else
                    {
                        $this->lastError = mysql_error();
                        
                        throw new exception( 'Failed to execute query for user "'.$key.'"' );
                    }
                }
                else
                {
                    try
                    {
                        $this->select( null, $key );
                        $this->query( $sql, $key );
                    }
                    catch( exception $e )
                    {
                        throw new exception( 'Query\'ed the database before selecting a database and failed to establish a link.' . $e->getMessage() );
                    }
                }
            }
            else
            {
                try
                {
                    $this->connect( $key );
                    $this->query( $sql, $key );
                }
                catch( exception $e )
                {
                    throw new exception( 'Query\'ed the database before connecting and failed to establish a connection. ' . $e->getMessage() );
                }            
            }        
        }
        
        # Store the query and sql into the class if successfull.
        public function sQuery( $name, $sql, $key = null )
        {
            try
            {
                $this->query( $sql, $key );
                
                $this->SQL[$name]     = $this->lastSQL;
                $this->result[$name]     = $this->lastResult;
            }
            catch( exception $e )
            {
                throw new exception( $e->getMessage() );
            }
        }
        
        public function escape( $array, $escape = true )
        {
            foreach( $array as $key => $value )
            {
                $array[$key] = $escape ? addslashes( $value ) : stripslashes( $value );
            }
            
            return $array;
        }
        
        public function __construct()
        {
            $this->pEscape = ! get_magic_quotes_gpc();
        
            $temp = array
            (
                'post'        =>    isset( $_POST ) ? $_POST : $_HTTP_POST_VARS,
                'get'        =>    isset( $_GET ) ? $_GET : $_HTTP_GET_VARS,
                'cookie'    =>    isset( $_COOKIE ) ? $_COOKIE : $_HTTP_COOKIE_VARS,
            );
            
            foreach( $temp as $key=> $value )
            {
                $temp[$key] = $this->escape( $value, $this->pEscape );
            }
                
            $this->post         = $temp['post'];
            $this->get            = $temp['get'];
            $this->cookie    = $temp['cookie'];
        }
    }

?>


Example Script:
Ik heb hem niet uitgetest, maar dit is ongeveer hoe je het kan gebruiken.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php
    
    try
    {
        $db = new database;
        
        $db->user_create( 'f', 'localhost', 'full', 'password 1' );
        $db->user_create( 'w', 'localhost', 'write', 'password 2' );
        $db->user_create( 'r', 'localhost', 'read', 'password 3', 'MyDatabase' );
        
        $db->query( 'SELECT * FROM table WHERE row = \'value\'', 'f' );                     // Errors, no database selected.
        if( $db->sQuery( 'shit', 'SELECT * FROM table WHERE row = \'value\'' ) )        // Correct
        {
            echo $db->lastSQL . '<br />';
        }
        else
        {
            trigger_error( $e->getMessage() . '<br />Optional and Obselecte Message to users: ' . $db->lastError );
        }
        
        while( $row = mysql_fetch_assoc( $db->lastResult )
        {
            // Meh.
        }
        
        $db->select( 'SomeDatabase', 'f' )
        $db->query( 'SELECT * FROM table WHERE row = \'value\'', 'f' );                // Works
        
        while( $row = mysql_fetch_array( $db->result['shit'] ) )
        {
            // Dunno if this is the correct way to loop a query, just a quick glinch on how it works...
        }
        
    }

?>
Gewijzigd op 01/01/1970 01:00:00 door Johan Kruse
 
PHP hulp

PHP hulp

25/05/2012 05:42:41
Gesponsorde koppelingen:
BHosted Hosting al vanaf € 1,- per maand

Controleer nu gratis jouw domeinnaam:

  
 
Johan Kruse

Johan Kruse

17/06/2009 13:52:00
Quote Anchor link
Bumpzors.
 
Emmanuel Delay

Emmanuel Delay

17/06/2009 15:09:00
Quote Anchor link
Wat ik bv. heb, is een methode select, maar dan
bv.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
<?php
$m_db = new dbClass();  // in de constructor worden ook de connectie gegevens uit een config file gehaald
$sql = "SELECT id, naam, email FROM leden WHERE naam = :naam OR email = :email";
$leden = $m_db->select($sql, array('naam', 'email'), array($_POST['naam'], $_POST['email']) );
?>


$leden krijgt dan als waarde een 2D array ([$row index][$row gegevens]) met de gefetchte resultaten.
Bv. $leden[1] is dan de tweede rij.
De beveiliging (tegen injection en zo) gebeurt binnen de select functie.
Je moet dus, buiten de class, niet meer prutsen met escape functies en zo. Gewoon rechtstreeks de $_POST variabele gebruiken.

Bij mijn insert functie laat ik ook toe om meerdere inserts te doen; daar geef je als derde parameter een 2D array mee.
Die functie geeft de last inserted id terug (of false).

Bij mij gebeurt dat allemaal via pdo met prepared statements (zie tutorial), maar dat hoeft uiteraard niet.

Misschien kan je er ook aan denken om de functionaliteit wat uit te breiden.
De bedoeling is dat je zo veel mogelijk in de class regelt, zodat je zo weinig mogelijk nog er buiten moet doen.

Verder ziet je code er wel proper uit, op het eerste zicht.
Gewijzigd op 01/01/1970 01:00:00 door Emmanuel Delay
 
Johan Kruse

Johan Kruse

17/06/2009 15:33:00
Quote Anchor link
Dat is erg interessant, dat PDO. Meerdere database types behandelen in 1 class, daar ga ik nog wel even wat aan doen.
Ik was zelf wel van plan om er wat meer functionaliteit erbij te schroeven, maar als de basis rot is, dan word de rest ook gewoon slecht.

Buiten de class was het niet de bedoeling om met escape functies te gaan werken, maar ik wist niet precies of het mogelijk was om gewoon de query string te escapen als het nodig was, of dat ik allemaal losse variables most escapen.

Ik heb best wel veel programmeer ervaring, en logica zegt dat alle variabels lost van elkaar geescaped moeten worden, dus ik zocht zelf nog een betere methode om de dingen te escapen.

Bedankt voor de informatie, ik ga er meteen even aan werken.
Gewijzigd op 01/01/1970 01:00:00 door Johan Kruse
 
Emmanuel Delay

Emmanuel Delay

17/06/2009 15:38:00
Quote Anchor link
Met PDO prepared statements ben je sowieso beveiligd tegen injection, uitleg daarover vind je in die tut.
 
RvW Of toch niet

RvW Of toch niet

17/06/2009 15:46:00
Quote Anchor link
http://www.phphulp.nl/php/scripts/1/1376/
kijk hier eens naar :)
 
GaMer B

GaMer B

17/06/2009 16:07:00
Quote Anchor link
Je private function genaamd key() gaat denk ik niet werken aangezien key() van zich zelf al een PHP functie is..?
 
Jelmer rrrr

Jelmer rrrr

17/06/2009 16:57:00
Quote Anchor link
Dat is op zich geen probleem. Je method-namen mogen best dezelfden zijn als die van functies. Keywords zoals include, switch, case of break zullen niet gaan.
 
GaMer B

GaMer B

17/06/2009 17:16:00
Quote Anchor link
Jelmer schreef op 17.06.2009 16:57:
Dat is op zich geen probleem. Je method-namen mogen best dezelfden zijn als die van functies. Keywords zoals include, switch, case of break zullen niet gaan.

Okej, weet ik dat ook weer :]
 
Emmanuel Delay

Emmanuel Delay

17/06/2009 18:02:00
Quote Anchor link
Ja, php gaat daar allemaal losser mee om dan (ik zeg maar iets) c++.
 
Mark PHP

Mark PHP

18/06/2009 16:21:00
Quote Anchor link
Kleine tip (regel 121):
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
<?php
$this->pData[$key] = array
(
  self::HOSTNAME     => $hostname,
  self::USERNAME     => $username,
  self::PASSWORD     => $password,
  self::DATABASE     => $database,
);
 ?>
Gewijzigd op 01/01/1970 01:00:00 door Mark PHP
 



Overzicht Reageren