WordPress
Interact with your WordPress website using SQL Server
- Install the SQLHTTP database on your SQL Server
- Install the WordPress WP REST API – OAuth 1.0a Server
- From WordPress dashboard, navigate to Users, Applications, and then register a new application with a call back such as http://localhost:53200
- Copy your newly generated Client Key and Client Secret
- Create the stored procedures documented below
- Execute the following SQL statement:
|
1 2 3 4 5 6 7 |
EXEC usp_WordPress_v2_Auth_Init @Profile = 'enter-profile-name-of-your-choosing', @RootURL = 'enter-root-web-address', @ClientKey = 'enter-client-key', @ClientSecret = 'enter-client-secret', @RedirectURL = 'enter-redirecturl' --example: http://localhost:53200 |
|
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 |
CREATE PROCEDURE usp_WordPress_v2_Auth_Init( @Profile nvarchar(100), @RootURL nvarchar(200), @ClientKey varchar(4000), @ClientSecret varchar(4000), @RedirectURL varchar(100) = 'http://localhost:53200') AS SET NOCOUNT ON EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RootURL', @Value = @RootURL EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientKey', @Value = @ClientKey EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Token', @Value = '' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenSecret', @Value = '' DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) SET @URL = @RootURL EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'oauth1', 'request' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_WordPress_v2_Auth_Header @HttpSessionID, @Profile, @URL, 'POST' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @Token varchar(4000) DECLARE @TokenSecret varchar(4000) DECLARE @RedirectURLCode varchar(1000) DECLARE @UserBrowseToURL varchar(MAX) DECLARE @QueryString nvarchar(MAX) SET @Token = SQLHTTP.net.MidText(@Response, 'oauth_token=', '&', 1) SET @TokenSecret = SQLHTTP.net.MidText(@Response, 'oauth_token_secret=', '&', 1) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Token', @Value = @Token EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenSecret', @Value = @TokenSecret SET @UserBrowseToURL = @RootURL EXEC SQLHTTP.net.UrlBuilder @UserBrowseToURL OUTPUT, @Profile, 'oauth1', 'authorize' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'oauth_token', @Token, 'oauth_token_secret', @TokenSecret --parameter that should not be encoded SET @QueryString = @QueryString + '&oauth_callback=' COLLATE SQL_Latin1_General_CP1_CI_AS + @RedirectURL SET @UserBrowseToURL = @UserBrowseToURL + @QueryString DECLARE @Timeout int DECLARE @TimeoutReached bit = 0 SET @Timeout = 180 --three minutes wait EXEC SQLHTTP.net.AuthListener @UserBrowseToURL = @UserBrowseToURL, @RedirectURL = @RedirectURL, @Timeout = @Timeout, @QueryString = @QueryString OUTPUT, @TimeoutReached = @TimeoutReached OUTPUT IF @TimeoutReached = 1 BEGIN RAISERROR('Timeout reached waiting for browser authentication', 16, 1) RETURN END SET @RedirectURLCode = SQLHTTP.net.Split(@QueryString, 'oauth_verifier=', 2) SET @RedirectURLCode = SQLHTTP.net.Split(@RedirectURLCode, '&', 1) SET @URL = @RootURL EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'oauth1', 'access' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'oauth_verifier', @RedirectURLCode SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_WordPress_v2_Auth_Header @HttpSessionID, @Profile, @URL, 'GET' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT SET @Token = SQLHTTP.net.MidText(@Response, 'oauth_token=', '&', 1) SET @TokenSecret = SQLHTTP.net.Split(@Response, 'oauth_token_secret=', 2) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Token', @Value = @Token EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenSecret', @Value = @TokenSecret END GO |
|
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 |
CREATE PROCEDURE usp_WordPress_v2_Auth_Header( @HttpSessionID uniqueidentifier, @Profile nvarchar(100), @URL nvarchar(4000), @Method varchar(10)) AS DECLARE @ClientKey varchar(4000) DECLARE @ClientSecret varchar(4000) DECLARE @Token varchar(4000) DECLARE @TokenSecret varchar(4000) SET @ClientKey = SQLHTTP.net.AuthParam(@Profile, 'ClientKey') SET @ClientSecret = SQLHTTP.net.AuthParam(@Profile, 'ClientSecret') SET @Token = SQLHTTP.net.AuthParam(@Profile, 'Token') SET @TokenSecret = SQLHTTP.net.AuthParam(@Profile, 'TokenSecret') EXEC SQLHTTP.net.OAuthHeader @HttpSessionID = @HttpSessionID, @URL = @URL, @Method = @Method, @ConsumerKey = @ClientKey, @ConsumerSecret = @ClientSecret, @Token = @Token, @TokenSecret = @TokenSecret GO |
- Categories – Fetch
- Category – Create
- Category – Delete
- Category – Fetch
- Category – Update
- Comment – Create
- Comment – Delete
- Comment – Fetch
- Comment – Update
- Comments – Fetch
- Media – Delete
- Media – Download
- Media – Fetch
- Media – Update
- Media – Upload
- Media Item – Fetch
- Page – Create
- Page – Delete
- Page – Fetch
- Page – Update
- Pages – Fetch
- Post – Create
- Post – Delete
- Post – Fetch
- Post – Update
- Post Type – Fetch
- Posts – Fetch
- Settings – Update
- Status – Fetch
- Statuses – Fetch
- Tag – Create
- Tag – Delete
- Tag – Fetch
- Tag – Update
- Tags – Fetch
- Taxonomies – Fetch
- Taxonomy – Fetch
- Types – Fetch
- User – Create
- User – Delete
- User – Fetch
- User – Update
- Users – Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.