Fossil SCM

Add the ability for an administrator to run raw SQL commands via the web interface.

drh 2012-06-20 17:24 trunk
Commit ca0faa88a4ef6ef2a636587a340fe340c690f6c5
Parent c5d56e49746579e…
1 file changed +118
~ src/setup.c
M src/setup.c
+118
--- src/setup.c
+++ src/setup.c
@@ -95,10 +95,12 @@
9595
 "A record of received artifacts and their sources");
9696
 setup_menu_entry("User-Log", "access_log",
9797
 "A record of login attempts");
9898
 setup_menu_entry("Stats", "stat",
9999
 "Display repository statistics");
100
+ setup_menu_entry("SQL", "admin_sql",
101
+ "Enter raw SQL commands");
100102
 @ </table>
101103
102104
 style_footer();
103105
 }
104106
@@ -1529,5 +1531,121 @@
15291531
 @ images, so you may need to press the Reload button before changes will
15301532
 @ take effect. </p>
15311533
 style_footer();
15321534
 db_end_transaction(0);
15331535
 }
1536
+
1537
+
1538
+/*
1539
+** WEBPAGE: admin_sql
1540
+**
1541
+** Run raw SQL commands against the database file using the web interface.
1542
+*/
1543
+void sql_page(void){
1544
+ const char *zQ = P("q");
1545
+ int go = P("go")!=0;
1546
+ login_check_credentials();
1547
+ if( !g.perm.Setup ){
1548
+ login_needed();
1549
+ }
1550
+ db_begin_transaction();
1551
+ style_header("Raw SQL Commands");
1552
+ @ <p><b>Caution:</b> There are no restrictions on the SQL that can be
1553
+ @ run by this page. You can do serious and irrepairable damage to the
1554
+ @ repository. Proceed with extreme caution.</p>
1555
+ @
1556
+ @ <p>Database names:<ul><li>repository &rarr; %s(db_name("repository"))
1557
+ if( g.configOpen ){
1558
+ @ <li>config &rarr; %s(db_name("configdb"))
1559
+ }
1560
+ if( g.localOpen ){
1561
+ @ <li>local-checkout &rarr; %s(db_name("localdb"))
1562
+ }
1563
+ @ </ul></p>
1564
+ @
1565
+ @ <form method="post" action="%s(g.zTop)/admin_sql">
1566
+ login_insert_csrf_secret();
1567
+ @ SQL:<br />
1568
+ @ <textarea name="q" rows="5" cols="80">%h(zQ)</textarea><br />
1569
+ @ <input type="submit" name="go" value="Run SQL">
1570
+ @ <input type="submit" name="schema" value="Show Schema">
1571
+ @ <input type="submit" name="tablelist" value="List Tables">
1572
+ @ </form>
1573
+ if( P("schema") ){
1574
+ zQ = sqlite3_mprintf(
1575
+ "SELECT sql FROM %s.sqlite_master WHERE sql IS NOT NULL",
1576
+ db_name("repository"));
1577
+ go = 1;
1578
+ }else if( P("tablelist") ){
1579
+ zQ = sqlite3_mprintf(
1580
+ "SELECT name FROM %s.sqlite_master WHERE type='table'"
1581
+ " ORDER BY name",
1582
+ db_name("repository"));
1583
+ go = 1;
1584
+ }
1585
+ if( go ){
1586
+ sqlite3_stmt *pStmt;
1587
+ int rc;
1588
+ const char *zTail;
1589
+ int nCol;
1590
+ int nRow = 0;
1591
+ int i;
1592
+ @ <hr />
1593
+ login_verify_csrf_secret();
1594
+ rc = sqlite3_prepare_v2(g.db, zQ, -1, &pStmt, &zTail);
1595
+ if( rc!=SQLITE_OK ){
1596
+ @ <div class="generalError">%h(sqlite3_errmsg(g.db))</div>
1597
+ sqlite3_finalize(pStmt);
1598
+ }else if( pStmt==0 ){
1599
+ /* No-op */
1600
+ }else if( (nCol = sqlite3_column_count(pStmt))==0 ){
1601
+ sqlite3_step(pStmt);
1602
+ rc = sqlite3_finalize(pStmt);
1603
+ if( rc ){
1604
+ @ <div class="generalError">%h(sqlite3_errmsg(g.db))</div>
1605
+ }
1606
+ }else{
1607
+ @ <table border=1>
1608
+ while( sqlite3_step(pStmt)==SQLITE_ROW ){
1609
+ if( nRow==0 ){
1610
+ @ <tr>
1611
+ for(i=0; i<nCol; i++){
1612
+ @ <th>%h(sqlite3_column_name(pStmt, i))</th>
1613
+ }
1614
+ @ </tr>
1615
+ }
1616
+ nRow++;
1617
+ @ <tr>
1618
+ for(i=0; i<nCol; i++){
1619
+ switch( sqlite3_column_type(pStmt, i) ){
1620
+ case SQLITE_INTEGER:
1621
+ case SQLITE_FLOAT: {
1622
+ @ <td align="right" valign="top">
1623
+ @ %s(sqlite3_column_text(pStmt, i))</td>
1624
+ break;
1625
+ }
1626
+ case SQLITE_NULL: {
1627
+ @ <td valign="top" align="center"><i>NULL</i></td>
1628
+ break;
1629
+ }
1630
+ case SQLITE_TEXT: {
1631
+ int k;
1632
+ const char *zText = (const char*)sqlite3_column_text(pStmt, i);
1633
+ @ <td align="left" valign="top"
1634
+ @ style="white-space:pre;">%h(zText)</td>
1635
+ break;
1636
+ }
1637
+ case SQLITE_BLOB: {
1638
+ @ <td valign="top" align="center">
1639
+ @ <i>%d(sqlite3_column_bytes(pStmt, i))-byte BLOB</i></td>
1640
+ break;
1641
+ }
1642
+ }
1643
+ }
1644
+ @ </tr>
1645
+ }
1646
+ sqlite3_finalize(pStmt);
1647
+ @ </table>
1648
+ }
1649
+ }
1650
+ style_footer();
1651
+}
15341652
--- src/setup.c
+++ src/setup.c
@@ -95,10 +95,12 @@
95 "A record of received artifacts and their sources");
96 setup_menu_entry("User-Log", "access_log",
97 "A record of login attempts");
98 setup_menu_entry("Stats", "stat",
99 "Display repository statistics");
 
 
100 @ </table>
101
102 style_footer();
103 }
104
@@ -1529,5 +1531,121 @@
1529 @ images, so you may need to press the Reload button before changes will
1530 @ take effect. </p>
1531 style_footer();
1532 db_end_transaction(0);
1533 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1534
--- src/setup.c
+++ src/setup.c
@@ -95,10 +95,12 @@
95 "A record of received artifacts and their sources");
96 setup_menu_entry("User-Log", "access_log",
97 "A record of login attempts");
98 setup_menu_entry("Stats", "stat",
99 "Display repository statistics");
100 setup_menu_entry("SQL", "admin_sql",
101 "Enter raw SQL commands");
102 @ </table>
103
104 style_footer();
105 }
106
@@ -1529,5 +1531,121 @@
1531 @ images, so you may need to press the Reload button before changes will
1532 @ take effect. </p>
1533 style_footer();
1534 db_end_transaction(0);
1535 }
1536
1537
1538 /*
1539 ** WEBPAGE: admin_sql
1540 **
1541 ** Run raw SQL commands against the database file using the web interface.
1542 */
1543 void sql_page(void){
1544 const char *zQ = P("q");
1545 int go = P("go")!=0;
1546 login_check_credentials();
1547 if( !g.perm.Setup ){
1548 login_needed();
1549 }
1550 db_begin_transaction();
1551 style_header("Raw SQL Commands");
1552 @ <p><b>Caution:</b> There are no restrictions on the SQL that can be
1553 @ run by this page. You can do serious and irrepairable damage to the
1554 @ repository. Proceed with extreme caution.</p>
1555 @
1556 @ <p>Database names:<ul><li>repository &rarr; %s(db_name("repository"))
1557 if( g.configOpen ){
1558 @ <li>config &rarr; %s(db_name("configdb"))
1559 }
1560 if( g.localOpen ){
1561 @ <li>local-checkout &rarr; %s(db_name("localdb"))
1562 }
1563 @ </ul></p>
1564 @
1565 @ <form method="post" action="%s(g.zTop)/admin_sql">
1566 login_insert_csrf_secret();
1567 @ SQL:<br />
1568 @ <textarea name="q" rows="5" cols="80">%h(zQ)</textarea><br />
1569 @ <input type="submit" name="go" value="Run SQL">
1570 @ <input type="submit" name="schema" value="Show Schema">
1571 @ <input type="submit" name="tablelist" value="List Tables">
1572 @ </form>
1573 if( P("schema") ){
1574 zQ = sqlite3_mprintf(
1575 "SELECT sql FROM %s.sqlite_master WHERE sql IS NOT NULL",
1576 db_name("repository"));
1577 go = 1;
1578 }else if( P("tablelist") ){
1579 zQ = sqlite3_mprintf(
1580 "SELECT name FROM %s.sqlite_master WHERE type='table'"
1581 " ORDER BY name",
1582 db_name("repository"));
1583 go = 1;
1584 }
1585 if( go ){
1586 sqlite3_stmt *pStmt;
1587 int rc;
1588 const char *zTail;
1589 int nCol;
1590 int nRow = 0;
1591 int i;
1592 @ <hr />
1593 login_verify_csrf_secret();
1594 rc = sqlite3_prepare_v2(g.db, zQ, -1, &pStmt, &zTail);
1595 if( rc!=SQLITE_OK ){
1596 @ <div class="generalError">%h(sqlite3_errmsg(g.db))</div>
1597 sqlite3_finalize(pStmt);
1598 }else if( pStmt==0 ){
1599 /* No-op */
1600 }else if( (nCol = sqlite3_column_count(pStmt))==0 ){
1601 sqlite3_step(pStmt);
1602 rc = sqlite3_finalize(pStmt);
1603 if( rc ){
1604 @ <div class="generalError">%h(sqlite3_errmsg(g.db))</div>
1605 }
1606 }else{
1607 @ <table border=1>
1608 while( sqlite3_step(pStmt)==SQLITE_ROW ){
1609 if( nRow==0 ){
1610 @ <tr>
1611 for(i=0; i<nCol; i++){
1612 @ <th>%h(sqlite3_column_name(pStmt, i))</th>
1613 }
1614 @ </tr>
1615 }
1616 nRow++;
1617 @ <tr>
1618 for(i=0; i<nCol; i++){
1619 switch( sqlite3_column_type(pStmt, i) ){
1620 case SQLITE_INTEGER:
1621 case SQLITE_FLOAT: {
1622 @ <td align="right" valign="top">
1623 @ %s(sqlite3_column_text(pStmt, i))</td>
1624 break;
1625 }
1626 case SQLITE_NULL: {
1627 @ <td valign="top" align="center"><i>NULL</i></td>
1628 break;
1629 }
1630 case SQLITE_TEXT: {
1631 int k;
1632 const char *zText = (const char*)sqlite3_column_text(pStmt, i);
1633 @ <td align="left" valign="top"
1634 @ style="white-space:pre;">%h(zText)</td>
1635 break;
1636 }
1637 case SQLITE_BLOB: {
1638 @ <td valign="top" align="center">
1639 @ <i>%d(sqlite3_column_bytes(pStmt, i))-byte BLOB</i></td>
1640 break;
1641 }
1642 }
1643 }
1644 @ </tr>
1645 }
1646 sqlite3_finalize(pStmt);
1647 @ </table>
1648 }
1649 }
1650 style_footer();
1651 }
1652

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button