# Enable authorizer-feature in PDO_SQLITE - Thread: https://externals.io/message/26640 - Original archive: https://news-web.php.net/php.internals/26640 - Started by Mario Wolff on 2006-11-16T13:05:10+00:00 - Messages: 11 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #26640 — Mario Wolff — 2006-11-16T13:05:10+00:00 URL: https://externals.io/message/26640#26640 Hello list, i've extended sqlite_driver to get access to the authorizer-feature of sqlite. This is my first contact with the Zend-API and my last c skill is more than a bit outdated. Could someone review/cleanup my code to get it merged to the distribution? Hint and comment welcome! Thanks, Mario Wolff PS: Patch applys against 5.2.0! --- ## #26641 — Wez Furlong — 2006-11-16T14:29:58+00:00 URL: https://externals.io/message/26640#26641 · In reply to: #26640 Two comments about the patch: - could you create a unified diff and post that instead? - static local variable usage is not thread safe; state should be stored in the pdo_dbh_t structure. And one concern: I deliberately left this feature unimplemented so far because the authorization callback will happen "a lot", and frequent callbacks into PHP script will make things slower. I suggest that you adjust your patch to cache the function callback information in the pdo_dbh_t to reduce some of that overhead, and run some benchmarks for a simple authorizer function in PHP that always returns true vs the same script with no authorizer, so that we get a feel for what kind of impact that has for various common queries. One other thing that is important is to ensure that the PHP safe_mode/open_basedir checks have higher priority than the PHP script callback. If safe_mode/open_basedir decide that the path is not accessible, then the PHP script must not be able to override that. However, if safe_mode/open_basedir say that access is ok, the PHP script can optionally override that decision. --Wez. > Hello list, > i've extended sqlite_driver to get access to the authorizer-feature of > sqlite. This is my first contact with the Zend-API and my last c skill > is more than a bit outdated. > Could someone review/cleanup my code to get it merged to the > distribution? Hint and comment welcome! > Thanks, > Mario Wolff > > PS: Patch applys against 5.2.0! --- ## #26642 — Mario Wolff — 2006-11-16T14:52:25+00:00 URL: https://externals.io/message/26640#26642 · In reply to: #26641 @Wez: Whoop, just found you as maintainer in Pecl and send the same request direct to you, sorry! 2006/11/16, Wez Furlong : > Two comments about the patch: > > - could you create a unified diff and post that instead? Here it is! > - static local variable usage is not thread safe; state should be > stored in the pdo_dbh_t structure. Makes sense, even for cleanup. pdo_sqlite_request_shutdown cant free the callback-zval right now. > And one concern: > > I deliberately left this feature unimplemented so far because the > authorization callback will happen "a lot", and frequent callbacks > into PHP script will make things slower. It is only called at compiletime of a query! I think thats not as hard! Maybe it could be an optional feature? > I suggest that you adjust your patch to cache the function callback > information in the pdo_dbh_t to reduce some of that overhead, and run > some benchmarks for a simple authorizer function in PHP that always > returns true vs the same script with no authorizer, so that we get a > feel for what kind of impact that has for various common queries. The Sqlite-Api keeps the user-authorizer in the userdata-void-pointer. The static definition is only ment for clean up! > One other thing that is important is to ensure that the PHP > safe_mode/open_basedir checks have higher priority than the PHP script > callback. If safe_mode/open_basedir decide that the path is not > accessible, then the PHP script must not be able to override that. > However, if safe_mode/open_basedir say that access is ok, the PHP > script can optionally override that decision. Have a look on my authorizer, the user-authorizer is only called if your rules gives SQLITE_OK. The user-authorizer is called from a default authorizer, not from the sqlite-api! > --Wez. Regards, Mario > > > > > […] --- ## #26643 — Mario Wolff — 2006-11-16T15:21:56+00:00 URL: https://externals.io/message/26640#26643 · In reply to: #26641 2006/11/16, Wez Furlong : > I suggest that you adjust your patch to cache the function callback > information in the pdo_dbh_t to reduce some of that overhead, and run > some benchmarks for a simple authorizer function in PHP that always > returns true vs the same script with no authorizer, so that we get a > feel for what kind of impact that has for various common queries. Is the pdo_sqlite_db_handle not the better place to store? I'm not so deep in the architecture to decide! > --Wez. --- ## #26644 — Mario Wolff — 2006-11-16T15:45:36+00:00 URL: https://externals.io/message/26640#26644 · In reply to: #26643 2006/11/16, Mario Wolff : > 2006/11/16, Wez Furlong : > > […] > > Is the pdo_sqlite_db_handle not the better place to store? I'm not so > deep in the architecture to decide! New patches attached! static-stuff removed. user_authorizer cleanup done. > > […] --- ## #26645 — Mario Wolff — 2006-11-16T15:52:52+00:00 URL: https://externals.io/message/26640#26645 · In reply to: #26644 Short test script: sqliteSetAuthorizer('auth'); $db->exec( "CREATE TABLE strings( a)"); $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); foreach ( $data as $str) { $insert->execute( array( $str)); } $insert = null; echo "unregister authorizer\n"; $db->sqliteSetAuthorizer(); function auth($type,$arga,$argb,$argc,$argd ){ echo "$type\t$arga\t$argb\t$argc\t$argd\n"; return true; } print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); ?> gives: register authorizer SQLITE_INSERT sqlite_master main SQLITE_CREATE_TABLE strings main SQLITE_UPDATE sqlite_master type main SQLITE_UPDATE sqlite_master name main SQLITE_UPDATE sqlite_master tbl_name main SQLITE_UPDATE sqlite_master rootpage main SQLITE_UPDATE sqlite_master sql main SQLITE_READ sqlite_master ROWID main SQLITE_READ sqlite_master name main SQLITE_READ sqlite_master rootpage main SQLITE_READ sqlite_master sql main SQLITE_READ sqlite_master tbl_name main SQLITE_INSERT strings main unregister authorizer Array ( [0] => Array ( [sqlite_version( *)] => 3.3.7 [0] => 3.3.7 ) ) --- ## #26646 — Wez Furlong — 2006-11-16T16:40:00+00:00 URL: https://externals.io/message/26640#26646 · In reply to: #26645 I think it would be better to pass in the pdo_dbh_t as the autharg to the C level callback and then use that to determine if any of the expensive work needs to be done in the callback. static int authorizer(....) { pdo_dbh_t *db; /* keep the current safemode / basedir checks "cheap" and fast */ if (existing safe_mode and open_base dir return SQLITE_DENY) { return SQLITE_DENY; } db = (pdo_dbh_t*)autharg; if (db->user_authorizer) { TSRMLS_FETCH(); ... free stuff } } You should take a look at how the pdo_sqlite_fci stuff works and adopt that for the authorizer callback, as it will help improve runtime performance there. It would also be best to register constants for the various SQLITE_XXX codes rather than creating strings and having the PHP code check against strings. This will also improve performance at runtime. --Wez. > Short test script: > > $data = array( 'one', 'two', 'three', 'four', 'five', 'six'); > $db = new PDO( 'sqlite::memory:'); > echo "register authorizer\n"; > $db->sqliteSetAuthorizer('auth'); > $db->exec( "CREATE TABLE strings( a)"); > $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); > foreach ( $data as $str) { > > $insert->execute( array( $str)); > } > $insert = null; > echo "unregister authorizer\n"; > $db->sqliteSetAuthorizer(); > > function auth($type,$arga,$argb,$argc,$argd ){ > echo "$type\t$arga\t$argb\t$argc\t$argd\n"; > return true; > } > print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); > > ?> > > gives: > register authorizer > SQLITE_INSERT sqlite_master main > SQLITE_CREATE_TABLE strings main > SQLITE_UPDATE sqlite_master type main > SQLITE_UPDATE sqlite_master name main > SQLITE_UPDATE sqlite_master tbl_name main > SQLITE_UPDATE sqlite_master rootpage main > SQLITE_UPDATE sqlite_master sql main > SQLITE_READ sqlite_master ROWID main > SQLITE_READ sqlite_master name main > SQLITE_READ sqlite_master rootpage main > SQLITE_READ sqlite_master sql main > SQLITE_READ sqlite_master tbl_name main > SQLITE_INSERT strings main > unregister authorizer > Array > ( > [0] => Array > ( > [sqlite_version( *)] => 3.3.7 > [0] => 3.3.7 > ) > > ) --- ## #26664 — Mario Wolff — 2006-11-17T22:02:39+00:00 URL: https://externals.io/message/26640#26664 · In reply to: #26646 2006/11/16, Wez Furlong : > I think it would be better to pass in the pdo_dbh_t as the autharg to > the C level callback and then use that to determine if any of the > expensive work needs to be done in the callback. Saves the sqlite_set_authorizer recall in SetAuthorizer, Looks simpler and cleaner. -> done > static int authorizer(....) > { > pdo_dbh_t *db; > > /* keep the current safemode / basedir checks "cheap" and fast */ > if (existing safe_mode and open_base dir return SQLITE_DENY) { > return SQLITE_DENY; > } > > db = (pdo_dbh_t*)autharg; > if (db->user_authorizer) { > TSRMLS_FETCH(); What is this? I'm not familiar with the hole php-stuff, sorry! Done by: + pdo_dbh_t *dbh; + pdo_sqlite_db_handle *H; ... + dbh=(pdo_dbh_t*)autharg; + PDO_CONSTRUCT_CHECK; + H = (pdo_sqlite_db_handle *)dbh->driver_data; + + if( H->user_authorizer ){ OK? > ... > > free stuff > } > } > > You should take a look at how the pdo_sqlite_fci stuff works and adopt > that for the authorizer callback, as it will help improve runtime > performance there. Can't find usefull stuff to adopt. Can you hint me further? > It would also be best to register constants for the various SQLITE_XXX codes > rather than creating strings and having the PHP code check against strings. > This will also improve performance at runtime. I like talking parameters! But you are right, performance comes first! -> done > > […] Modified: sqliteSetAuthorizer('auth'); $db->exec( "CREATE TABLE strings( a)"); $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); foreach ( $data as $str) { $insert->execute( array( $str)); } $insert = null; if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ if( $delete->execute(array( "six" ))){ echo "delete done!\n"; } $delete = null; }else{ echo "delete not prepared\n"; } echo "unregister authorizer\n"; $db->sqliteSetAuthorizer(); if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ if( $delete->execute(array( "six" ))){ echo "delete done!\n"; } $delete = null; }else{ echo "delete not prepared\n"; } function auth($type,$arga,$argb,$argc,$argd ){ echo "$type\t$arga\t$argb\t$argc\t$argd\n"; if( $type==SQLITE_DELETE ){ return SQLITE_DENY; } return SQLITE_OK; } print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); print_r( $db->query( 'SELECT * from strings;')->fetchAll( )); ?> > > […] now: register authorizer 18 sqlite_master main 2 strings main 23 sqlite_master type main 23 sqlite_master name main 23 sqlite_master tbl_name main 23 sqlite_master rootpage main 23 sqlite_master sql main 20 sqlite_master ROWID main 20 sqlite_master name main 20 sqlite_master rootpage main 20 sqlite_master sql main 20 sqlite_master tbl_name main 18 strings main 9 strings main delete not prepared unregister authorizer delete done! Array ( [0] => Array ( [sqlite_version( *)] => 3.3.7 [0] => 3.3.7 ) ) Array ( [0] => Array ( [a] => one [0] => one ) [1] => Array ( [a] => two [0] => two ) [2] => Array ( [a] => three [0] => three ) [3] => Array ( [a] => four [0] => four ) [4] => Array ( [a] => five [0] => five ) ) Regards, Mario --- ## #26679 — Mario Wolff — 2006-11-20T20:35:27+00:00 URL: https://externals.io/message/26640#26679 · In reply to: #26664 Any suggestions or meanings on it? Thanks, Mario 2006/11/17, Mario Wolff : > 2006/11/16, Wez Furlong : > > […] > > Saves the sqlite_set_authorizer recall in SetAuthorizer, Looks simpler > and cleaner. -> done > > > […] > > What is this? I'm not familiar with the hole php-stuff, sorry! > Done by: > + pdo_dbh_t *dbh; > + pdo_sqlite_db_handle *H; > ... > + dbh=(pdo_dbh_t*)autharg; > + PDO_CONSTRUCT_CHECK; > + H = (pdo_sqlite_db_handle *)dbh->driver_data; > + > + if( H->user_authorizer ){ > > OK? > > > > […] > > Can't find usefull stuff to adopt. Can you hint me further? > > > […] > > I like talking parameters! But you are right, performance comes first! -> done > > > […] > Modified: > $data = array( 'one', 'two', 'three', 'four', 'five', 'six'); > $db = new PDO( 'sqlite::memory:'); > echo "register authorizer\n"; > $db->sqliteSetAuthorizer('auth'); > $db->exec( "CREATE TABLE strings( a)"); > $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); > foreach ( $data as $str) { > > $insert->execute( array( $str)); > } > $insert = null; > > if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ > if( $delete->execute(array( "six" ))){ > echo "delete done!\n"; > } > $delete = null; > }else{ > echo "delete not prepared\n"; > } > > echo "unregister authorizer\n"; > $db->sqliteSetAuthorizer(); > > if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ > if( $delete->execute(array( "six" ))){ > echo "delete done!\n"; > } > $delete = null; > }else{ > echo "delete not prepared\n"; > } > > function auth($type,$arga,$argb,$argc,$argd ){ > echo "$type\t$arga\t$argb\t$argc\t$argd\n"; > if( $type==SQLITE_DELETE ){ > return SQLITE_DENY; > } > return SQLITE_OK; > } > print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); > print_r( $db->query( 'SELECT * from strings;')->fetchAll( )); > > ?> > > > […] > now: > register authorizer > 18 sqlite_master main > 2 strings main > 23 sqlite_master type main > 23 sqlite_master name main > 23 sqlite_master tbl_name main > 23 sqlite_master rootpage main > 23 sqlite_master sql main > 20 sqlite_master ROWID main > 20 sqlite_master name main > 20 sqlite_master rootpage main > 20 sqlite_master sql main > 20 sqlite_master tbl_name main > 18 strings main > 9 strings main > delete not prepared > unregister authorizer > delete done! > Array > ( > [0] => Array > ( > [sqlite_version( *)] => 3.3.7 > [0] => 3.3.7 > ) > > ) > Array > ( > [0] => Array > ( > [a] => one > [0] => one > ) > > [1] => Array > ( > [a] => two > [0] => two > ) > > [2] => Array > ( > [a] => three > [0] => three > ) > > [3] => Array > ( > [a] => four > [0] => four > ) > > [4] => Array > ( > [a] => five > [0] => five > ) > > ) > > Regards, > Mario --- ## #26899 — Mario Wolff — 2006-12-11T10:29:23+00:00 URL: https://externals.io/message/26640#26899 · In reply to: #26679 Any suggestions or meanings on it? Thanks, Mario Short test script: sqliteSetAuthorizer('auth'); $db->exec( "CREATE TABLE strings( a)"); $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); foreach ( $data as $str) { $insert->execute( array( $str)); } $insert = null; if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ if( $delete->execute(array( "six" ))){ echo "delete done!\n"; } $delete = null; }else{ echo "delete not prepared\n"; } echo "unregister authorizer\n"; $db->sqliteSetAuthorizer(); if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ if( $delete->execute(array( "six" ))){ echo "delete done!\n"; } $delete = null; }else{ echo "delete not prepared\n"; } function auth($type,$arga,$argb,$argc,$argd ){ echo "$type\t$arga\t$argb\t$argc\t$argd\n"; if( $type==SQLITE_DELETE ){ return SQLITE_DENY; } return SQLITE_OK; } print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); print_r( $db->query( 'SELECT * from strings;')->fetchAll( )); ?> gives: register authorizer 18 sqlite_master main 2 strings main 23 sqlite_master type main 23 sqlite_master name main 23 sqlite_master tbl_name main 23 sqlite_master rootpage main 23 sqlite_master sql main 20 sqlite_master ROWID main 20 sqlite_master name main 20 sqlite_master rootpage main 20 sqlite_master sql main 20 sqlite_master tbl_name main 18 strings main 9 strings main delete not prepared unregister authorizer delete done! Array ( [0] => Array ( [sqlite_version( *)] => 3.3.7 [0] => 3.3.7 ) ) Array ( [0] => Array ( [a] => one [0] => one ) [1] => Array ( [a] => two [0] => two ) [2] => Array ( [a] => three [0] => three ) [3] => Array ( [a] => four [0] => four ) [4] => Array ( [a] => five [0] => five ) ) --- ## #26946 — Wez Furlong — 2006-12-14T01:15:21+00:00 URL: https://externals.io/message/26640#26946 · In reply to: #26899 Hi Mario, Sorry that I didn't get back to you--I often get too busy to follow up on PHP related mail so you need to poke. I'm currently doing some work on PDO and so I'll look at integrating your patch this week; if I don't spot any problems, I'll commit it, otherwise I'll get back to you to discuss it. Thanks for putting in the effort! --Wez. > Any suggestions or meanings on it? > > Thanks, > Mario > > Short test script: > $data = array( 'one', 'two', 'three', 'four', 'five', 'six'); > $db = new PDO( 'sqlite::memory:'); > echo "register authorizer\n"; > $db->sqliteSetAuthorizer('auth'); > $db->exec( "CREATE TABLE strings( a)"); > $insert = $db->prepare( 'INSERT INTO strings VALUES ( ?)'); > foreach ( $data as $str) { > $insert->execute( array( $str)); > } > $insert = null; > > if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ > if( $delete->execute(array( "six" ))){ > echo "delete done!\n"; > } > $delete = null; > }else{ > echo "delete not prepared\n"; > } > > echo "unregister authorizer\n"; > $db->sqliteSetAuthorizer(); > > if( $delete = $db->prepare( 'DELETE FROM strings where a=?')){ > if( $delete->execute(array( "six" ))){ > echo "delete done!\n"; > } > $delete = null; > }else{ > echo "delete not prepared\n"; > } > > function auth($type,$arga,$argb,$argc,$argd ){ > echo "$type\t$arga\t$argb\t$argc\t$argd\n"; > if( $type==SQLITE_DELETE ){ > return SQLITE_DENY; > } > return SQLITE_OK; > } > print_r( $db->query( 'SELECT sqlite_version( *);')->fetchAll( )); > print_r( $db->query( 'SELECT * from strings;')->fetchAll( )); > > ?> > > gives: > register authorizer > 18 sqlite_master main > 2 strings main > 23 sqlite_master type main > 23 sqlite_master name main > 23 sqlite_master tbl_name main > 23 sqlite_master rootpage main > 23 sqlite_master sql main > 20 sqlite_master ROWID main > 20 sqlite_master name main > 20 sqlite_master rootpage main > 20 sqlite_master sql main > 20 sqlite_master tbl_name main > 18 strings main > 9 strings main > delete not prepared > unregister authorizer > delete done! > Array > ( > [0] => Array > ( > [sqlite_version( *)] => 3.3.7 > [0] => 3.3.7 > ) > > ) > Array > ( > [0] => Array > ( > [a] => one > [0] => one > ) > > [1] => Array > ( > [a] => two > [0] => two > ) > > [2] => Array > ( > [a] => three > [0] => three > ) > > [3] => Array > ( > [a] => four > [0] => four > ) > > [4] => Array > ( > [a] => five > [0] => five > ) > > )