Hello All, Ok, this function is being called by authentication.php.
If I comment out the call like this: // if($passfail == 1) // checkMissingWebSecretKeys(); Then the Shibboleth authentication succeeds without an error. I would really like to know the root cause of this error. Am I missing something ? -- Al Quiros Enterprise Systems From: Evelio Quiros <[email protected]> Reply-To: "[email protected]" <[email protected]> Date: Monday, October 15, 2018 at 2:37 PM To: "[email protected]" <[email protected]> Subject: Re: Shibboleth Auth not working for VCL v2.5 - Please help Hi All, The function that is throwing the error is in utils.php. Here is the code that the VCL DB complains about. I am trying to check all the DB references noted below. Any help on this problem is appreciated. //////////////////////////////////////////////////////////////////////////////// /// /// \fn checkMissingWebSecretKeys() /// /// \brief checks for any web servers missing secret key entries and creates /// them if possible /// //////////////////////////////////////////////////////////////////////////////// function checkMissingWebSecretKeys() { global $mode; $mycryptkeyid = getCryptKeyID(); $values = array(); $query = "SELECT ck.id as cryptkeyid, " . "ck.pubkey as cryptkey, " . "s.id as secretid, " . "s.cryptsecret AS mycryptsecret " . "FROM cryptkey ck " . "JOIN (SELECT secretid as id, cryptsecret " . "FROM cryptsecret " . "WHERE cryptkeyid = $mycryptkeyid) AS s " . "LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND cs.secretid = s.id) " . "WHERE ck.hosttype = 'web' AND " . "cs.secretid IS NULL AND " . "ck.id != $mycryptkeyid"; $qh = doQuery($query); while($row = mysql_fetch_assoc($qh)) { $secret = decryptSecretKey($row['mycryptsecret']); $encsecret = encryptSecretKey($secret, $row['cryptkey']); $values[] = "({$row['cryptkeyid']}, {$row['secretid']}, '$encsecret', '" . SYMALGO . "', '" . SYMOPT . "', " . SYMLEN . ")"; } if(empty($values)) { if($mode == 'checkMissingWebSecretKeys') { print "<h2>Update Missing Web Server Secret Keys</h2>\n"; print "There are no missing secret keys this server has access to."; } return; } addCryptSecretKeyUpdates($values); if($mode == 'checkMissingWebSecretKeys') { print "<h2>Update Missing Web Server Secret Keys</h2>\n"; print "Successfully updated any missing secret keys this server has access to."; } } -- Al Quiros Enterprise Systems From: Evelio Quiros <[email protected]> Reply-To: "[email protected]" <[email protected]> Date: Monday, October 15, 2018 at 8:43 AM To: "[email protected]" <[email protected]> Subject: Re: Shibboleth Auth not working for VCL v2.5 - Please help Hi Josh, Yes, there is a table called “cryptkey”, and the table contains “1” as the id for the “web” host type. Also, in that .ht-inc/cryptkey/cryptkeyid, there is a value of “1” in the file. That is the only content in that file. The value of .ht-inc/cryptkey/cryptkeyid = 1 In that directory is also a private key file. cryptsecret table is empty. shibauth table is empty. The the table cryptkey, row 1 id = 1 hosted = 1 hosttype = web pubkey = -----BEGIN PUBLIC KEY----- algorithm = RSA algorithmoption = OEAP Keylength = 4096 In comparison, there is no cryptkey table in the previous version of VCL, which works OK for shibboleth. For the code in .ht_inc/authmethods directory, In shibauth.php, it did go so far as to put my username in the user table. Can you tell me where the code is that generates this SQL command ? Perhaps there is a missing variable that causes this error ? This is the error message: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS s LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND cs.secretid = s.id' at line 1 SELECT ck.id as cryptkeyid, ck.pubkey as cryptkey, s.id as secretid, s.cryptsecret AS mycryptsecret FROM cryptkey ck JOIN (SELECT secretid as id, cryptsecret FROM cryptsecret WHERE cryptkeyid = ) AS s LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND cs.secretid = s.id) WHERE ck.hosttype = 'web' AND cs.secretid IS NULL AND ck.id != ERROR(101): General MySQL error Mode was Backtrace: =-=-=-=-=-=-=-=-=-=-=-= Call#:1 => index.php:addLoginLog() (line#:187) Call#:2 => authentication.php:checkMissingWebSecretKeys() (line#:580) Call#:3 => utils.php:doQuery() (line#:3075) Backtrace with Arguments: =-=-=-=-=-=-=-=-=-=-=-= Call#:1 => index.php:addLoginLog() (line#:187) Arguments(4) Argument#: 1 => evquir@FIU Argument#: 2 => shibboleth Argument#: 3 => 3 Argument#: 4 => 1 ----------------------- Call#:2 => authentication.php:checkMissingWebSecretKeys() (line#:580) Arguments(none): ----------------------- Call#:3 => utils.php:doQuery() (line#:3075) Arguments(1) Argument#: 1 => SELECT ck.id as cryptkeyid, ck.pubkey as cryptkey, s.id as secretid, s.cryptsecret AS mycryptsecret FROM cryptkey ck JOIN (SELECT secretid as id, cryptsecret FROM cryptsecret WHERE cryptkeyid = ) AS s LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND cs.secretid = s.id) WHERE ck.hosttype = 'web' AND cs.secretid IS NULL AND ck.id != ----------------------- -- Al Quiros Enterprise Systems On 10/12/18, 4:33 PM, "Josh Thompson" <[email protected]> wrote: Hi Al, Is this system a fresh install of VCL 2.5 or was it upgraded from a previous version? Here are a few things to check: 1) that you have a table named cryptkey in your database and that there is a record in it 2) that .ht-inc/cryptkey/cryptkeyid exists and has a value in it that matches the id of a record in the cryptkey table Josh On Friday, October 12, 2018 3:02:26 PM EDT Evelio Quiros wrote: > Just FYI, I ran testsetup.php on the installation, and it all came back ok: > > PHP version: 5.4.16 > > Including .ht-inc/conf.php ... > > successfully included .ht-inc/conf.php > > Checking COOKIEDOMAIN setting in .ht-inc/conf.php ... > > COOKIEDOMAIN (webvcl01.fiu.edu) appears to be set correctly > > Checking that BASEURL in conf.php is set to use https ... > > BASEURL correctly set to use https > > Checking that SCRIPT is set appropriately ... > > SCRIPT appears to be set correctly > > Checking that other required constants are defined ... > > All required constants are defined in .ht-inc/conf.php > > Checking that .ht-inc/maintenance directory exists ... > > .ht-inc/maintenance directory exists > > Checking that .ht-inc/maintenance directory is writable ... > > maintenance directory is writable > > Checking that .ht-inc/cryptkey directory exists ... > > .ht-inc/cryptkey directory exists > > Checking that .ht-inc/cryptkey directory is writable ... > > cryptkey directory is writable > > Checking asymmetric encryption key for this web server ... > > Asymmetric key validated > > Testing for required php extensions ... > > All required modules are installed > > Checking values in .ht-inc/secrets.php ... > > all required values in .ht-inc/secrets.php appear to be set > > Testing mysql connection ... > > Successfully connected to mysql on dbvcl01.fiu.edu > Successfully selected database (vcl) on dbvcl01.fiu.edu > > Testing symmetric encryption ... > > Successfully encrypted test string > Successfully decrypted test string > > Testing asymmetric encryption key files ... > > successfully created private key from private key file > successfully created public key from public key file > > Testing asymmetric encryption ... > > successfully encrypted test string > successfully decrypted test string > > Testing for existance of dojo directory ... > > dojo directory exists > dojo directory is readable > > Testing for existance of spyc 0.5.1 and Spyc.php ... > > spyc directory exists > spyc directory is readable > .ht-inc/spyc-0.5.1/Spyc.php file exists > .ht-inc/spyc-0.5.1/Spyc.php is readable > > Checking themes for dojo css ... > > themes/default has had dojo css copied to it > themes/dropdownmenus has had dojo css copied to it > > Checking value of PHP display_errors ... > > display_errors: disabled > NOTE: Displaying errors in a production system is a security risk; > however, while getting VCL up and running, having them displayed makes > debugging a little easier. Edit your php.ini file to modify this setting. > Done > > -- Al Quiros > Enterprise Systems > > > > On 10/12/18, 2:21 PM, "Evelio Quiros" <[email protected]> wrote: > > Hi Josh, > > Yes, cryptkey is owned by apache and writable: > > drwxr-xr-x 2 apache apache 57 Sep 13 12:49 cryptkey > > Also the files in cryptkey are writable by apache: > > -rw-r--r-- 1 apache apache 1 Sep 13 12:49 cryptkeyid > -rw------- 1 apache apache 3394 Sep 13 12:49 private.pem > > Thanks for your help with this, Josh. > Sorry to be such a pain. > > -- Al Quiros > Enterprise Systems > > > > On 10/12/18, 1:37 PM, "Josh Thompson" <[email protected]> wrote: > > Hi Al, > > It sounds like your .ht-inc/cryptkey directory on the web server is > not writable. The testsetup.php script should report if it is or not. > Manually making this writable is described toward the end of step 2 under > "Install and Configure the Web Components" at this URL: > > https://vcl.apache.org/docs/VCL25InstallGuide.html > > Josh > > On Friday, October 12, 2018 7:50:59 AM EDT Evelio Quiros wrote: > > > Hi All, > > > > Could there be some missing field in my configuration that is > > causing this > > ? > > Or could it be a code issue with the shibboleth authentication part > ? > > It’s really strange that the shib auth throws a SQL error. > > > > > > When I try to log in to the new VCL installation, it does take > > me to my > > > > authentication page, where I enter my credentials. > > That part works fine. > > > It’s only when I get redirected back that I get an error message > > on the > > browser, then an email with the message below: The error message > > doesn’t > > seem to include the entire MySQL line, just a piece of it. Is > > there a piece of the shibboleth configuration that I am missing > > ? What are the > > “WebSecrectKeys” that the backtrace is mentioning ? Could it be > > the > > shibboleth authentication token ? > > > > > > > > You have an error in your SQL syntax; check the manual that > > corresponds to > > your MariaDB server version for the right syntax to use near ') AS > > s LEFT > > JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND cs.secretid = > > s.id' at > > line 1 > > > > > SELECT ck.id as cryptkeyid, ck.pubkey as cryptkey, > > s.id as > > > > secretid, s.cryptsecret AS mycryptsecret FROM cryptkey ck JOIN > > (SELECT > > secretid as id, cryptsecret FROM cryptsecret WHERE cryptkeyid = ) > > AS s LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND > > cs.secretid = s.id) WHERE ck.hosttype = 'web' AND cs.secretid IS > > NULL AND ck.id != > > > > > > > > > -- Al Quiros > > Enterprise Systems > > > > > > > > > > > > On 10/11/18, 1:21 PM, "Evelio Quiros" <[email protected]> wrote: > > > > > > Hi All, > > > > I am working on a new VCL 2.5 installation using > > Shibboleth. > > > > The test script in the documentation seems to work > > correctly. > > But when I try to log into the new VCL using Shibboleth, I > > get a > > > > MySQL error: > > > > > You have an error in your SQL syntax; check the manual > > that > > > > corresponds to your MariaDB server version for the right syntax to > > use near ') AS s LEFT JOIN cryptsecret cs ON (ck.id = > > cs.cryptkeyid AND cs.secretid = s.id' at line 1 > > SELECT ck.id as cryptkeyid, ck.pubkey as cryptkey, s.id > > > as secretid, s.cryptsecret AS mycryptsecret FROM cryptkey ck JOIN > > (SELECT > > secretid as id, cryptsecret FROM cryptsecret WHERE cryptkeyid = ) > > AS s LEFT JOIN cryptsecret cs ON (ck.id = cs.cryptkeyid AND > > cs.secretid = s.id) WHERE ck.hosttype = 'web' AND cs.secretid IS > > NULL AND ck.id != ERROR(101): General MySQL error > > > > Mode was > > > > Backtrace: > > =-=-=-=-=-=-=-=-=-=-=-= > > Call#:1 => index.php:addLoginLog() (line#:187) > > Call#:2 => > > authentication.php:checkMissingWebSecretKeys() > > > > (line#:580) > > Call#:3 => utils.php:doQuery() (line#:3075) > > > > > Backtrace with Arguments: > > =-=-=-=-=-=-=-=-=-=-=-= > > Call#:1 => index.php:addLoginLog() (line#:187) > > Arguments(4) > > > > Argument#: 1 => evquir@FIU > > Argument#: 2 => shibboleth > > Argument#: 3 => 3 > > Argument#: 4 => 1 > > ----------------------- > > Call#:2 => > > authentication.php:checkMissingWebSecretKeys() > > > > (line#:580) > > Arguments(none): > > > ----------------------- > > Call#:3 => utils.php:doQuery() (line#:3075) > > Arguments(1) > > > > Argument#: 1 => SELECT ck.id as cryptkeyid, ck.pubkey > > as > > > > cryptkey, s.id as secretid, s.cryptsecret AS mycryptsecret FROM > > cryptkey ck JOIN (SELECT secretid as id, cryptsecret FROM > > cryptsecret WHERE cryptkeyid = ) AS s LEFT JOIN cryptsecret cs ON > > (ck.id = cs.cryptkeyid AND cs.secretid = s.id) WHERE ck.hosttype > > = 'web' AND cs.secretid IS NULL AND ck.id != > > ----------------------- > > > > > > > > Any ideas on what could be causing this issue ? > > > > Thanks, > > -- Al Quiros > > Enterprise Systems > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > ------------------------------- > Josh Thompson > Systems Programmer > Platform Computing | VCL Developer > North Carolina State University > > [email protected] > 919-515-5323 > > my GPG/PGP key can be found at pgp.mit.edu > > All electronic mail messages in connection with State business > which are sent to or received by this account are subject to the NC Public > Records Law and may be disclosed to third parties. > > > -- ------------------------------- Josh Thompson Systems Programmer Platform Computing | VCL Developer North Carolina State University [email protected] 919-515-5323 my GPG/PGP key can be found at pgp.mit.edu All electronic mail messages in connection with State business which are sent to or received by this account are subject to the NC Public Records Law and may be disclosed to third parties.
