SQL Injection penn testing from the queries only

Is there an established method or tool available to perform penn testing on an application by only testing queries it sends to the database?

For example, if I have a bunch of SQL Servers hosting various websites and a query came through that wasn’t parameterised, is there a way I can detect these?

example query that probably isn’t secure: SELECT x,y,z FROM logins WHERE username = ‘xx’ and password = ‘yyy’

..instead I would expect a secure application to be probably be using sp_executesql

Is there a data structure that can perform range modulo additions and range minimum queries?

It is well-known that the Segment Tree performs range additions and range minimum queries in O(logN) each.

Let each element in the array have value V[i], M[i]. We define a “range modulo add” as the following: add +X to V[i] for each element in the range L<=i<=R and then calculate modulo M[i] for each element L<=i<=R. Can both this operation and range minimum queries be run in (worst-case or average-case) o(N)? If not on ranges [L,R], is it possible to handle range minimum queries and range modulo adds on the entire array quickly?

Use of CSS media queries with Customiser and preprocessor?

I’m building a WordPress theme, which is, of course, responsive. In my CSS I’ve used media queries, to differentiate styles between desktop and mobile. Currently I’m making the theme compatible with the customiser.

I read that styles added via the customiser appear in the head of a page. Now I’m wondering how to use media queries with those styles. Some divs are used on both mobile and desktop, but with different styling. Let’s say I have a div X and in the customiser I need the following:

1) A setting with its control to change a few styles of X, online on mobile. Let’s say @media (max-width: 1199px). 2) I also need a setting with a control to change the style of div X on desktop, min-width of 1200px.

How can I accomplish this?

I’m also open to using a CSS preprocessor, like LESS or SCSS. The value set in the customiser would then become a variable. What is your advice on this and could you please give me an example (e.g. a Color Picker)? Thank a lot!

Multisite Woocommerce – Slow Database Queries

These DB queries takes almost 20 second to load and i’m trying to understand what is causing this. Why there are so many question marks? Is it possible to optimize this somehow? The site is using MultilingualPress with Woocommerce.

This are queries for archive product for example

    SELECT SQL_CALC_FOUND_ROWS  wp_?_posts.ID FROM wp_?_posts  INNER JOIN wp_?_postmeta ON ( wp_?_posts.ID = wp_?_postmeta.post_id )  INNER JOIN wp_?_postmeta AS mt? ON ( wp_?_posts.ID = mt?.post_id ) WHERE ?=?  AND (    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             )    AND    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             ) ) AND (    (      ( wp_?_postmeta.meta_key = ? AND wp_?_postmeta.meta_value != ? )      OR      ( mt?.meta_key = ? AND mt?.meta_value != ? )   ) ) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ? OR wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY wp_?_posts.menu_order ASC, wp_?_posts.post_title ASC LIMIT ?, ? 

Another one

    SELECT SQL_CALC_FOUND_ROWS  wp_?_posts.ID FROM wp_?_posts  INNER JOIN wp_?_postmeta ON ( wp_?_posts.ID = wp_?_postmeta.post_id )  INNER JOIN wp_?_postmeta AS mt? ON ( wp_?_posts.ID = mt?.post_id ) WHERE ?=?  AND wp_?_posts.ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) AND (    wp_?_posts.ID NOT IN (                 SELECT object_id                 FROM wp_?_term_relationships                 WHERE term_taxonomy_id IN (?)             ) ) AND (    (      ( wp_?_postmeta.meta_key = ? AND wp_?_postmeta.meta_value != ? )      OR      ( mt?.meta_key = ? AND mt?.meta_value != ? )   ) ) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ? OR wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY wp_?_posts.menu_order ASC, wp_?_posts.post_title ASC LIMIT ?, ? 

Weird DNS queries to messager.xicp.net

A DNS query for this domain (messager.xicp.net) was classified and detected as “Trojan.Generic.DNS” by the FireEye NX in our network.

So, I used both VirusTotal and abuseibdb sites to check this domain. It seems it’s mapped to 127.0.0.1 (localhost) and when I ping this domain it pings my localhost IP. Could you explain this behavior?

I tried using viewDNS to trace route and I got this output:

traceroute to messager.xicp.net (127.0.0.1), 30 hops max, 60 byte packets 1 obfuscated.internal.network.com (0.0.0.0) 0.000 ms 0.000 ms 0.000 ms 2 obfuscated.internal.network.com (0.0.0.0) 1.000 ms 1.000 ms 1.000 m

Could you please explain this behavior, why it’s mapped to 127.0.0.1 (It wasn’t like this according to the results from VirusTotal as it was mapped to this IP 174.128.255.245 on 2018-04-11)

Slow queries constantly getting stuck on WordPress database of ~100,000 posts

I am constantly getting stuck SELECT queries on my wordpress databases like the following:

enter image description here

Many of the SELECT queries that get stuck are fairly ordinary, such as pulling the last 10 posts of an author or pulling 10 posts in a category – these are normal WordPress core queries, not from any plugin. My WordPress databases that get afflicted with this have around 100,000 rows in the wp_posts table, with a size of around 1GB. This is an example of the largest tables from one of the databases:

enter image description here

My dedicated server is has 4 CPU cores @ 3.4 GHz and 8 GB DDR4 RAM. For this server and these databases, should these kinds of issues be happening? What can I do to make normal WordPress queries always run without getting stuck? I have tried changing from MyISAM to InnoDB with no effect, as well as changing different settings in my.cnf – here it is currently:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid log-error=/var/lib/mysql/errorlog.err performance-schema=0 default-storage-engine=MyISAM max_allowed_packet=268435456 open_files_limit=10000 slow_query_log=ON log_slow_verbosity=1 innodb_buffer_pool_size=1G aria_pagecache_buffer_size=512M query_cache_size=0 query_cache_type=0 query_cache_limit=0 join_buffer_size=512K tmp_table_size=32M max_heap_table_size=32M table_definition_cache=1200 

And the result of running https://github.com/major/MySQLTuner-perl:

[root@hostname ~]# perl mysqltuner.pl --host 127.0.0.1  >>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>  >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/  >>  Run with '--help' for additional options and output filtering  [--] Skipped version check for MySQLTuner script [--] Performing tests on 127.0.0.1:3306 [OK] Currently running supported MySQL version 10.3.20-MariaDB-log [OK] Operating on 64-bit architecture  -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/lib/mysql/errorlog.err exists [--] Log file: /var/lib/mysql/errorlog.err(764K) [OK] Log file /var/lib/mysql/errorlog.err is readable. [OK] Log file /var/lib/mysql/errorlog.err is not empty [OK] Log file /var/lib/mysql/errorlog.err is smaller than 32 Mb [!!] /var/lib/mysql/errorlog.err contains 1571 warning(s). [!!] /var/lib/mysql/errorlog.err contains 1424 error(s). [--] 124 start(s) detected in /var/lib/mysql/errorlog.err [--] 1) 2019-11-25  6:31:15 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2019-11-25  6:08:45 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2019-11-25  5:47:35 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2019-11-25  5:34:11 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2019-11-25  5:22:58 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2019-11-25  5:02:11 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2019-11-25  4:33:46 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2019-11-25  4:27:54 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2019-11-25  4:21:59 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2019-11-25  4:21:52 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 123 shutdown(s) detected in /var/lib/mysql/errorlog.err [--] 1) 2019-11-25  6:31:08 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2019-11-25  6:08:26 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2019-11-25  5:47:27 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2019-11-25  5:33:59 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2019-11-25  5:22:55 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2019-11-25  5:02:05 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2019-11-25  4:33:42 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2019-11-25  4:27:47 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2019-11-25  4:21:58 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2019-11-25  4:21:48 0 [Note] /usr/sbin/mysqld: Shutdown complete  -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE  [--] Data in MyISAM tables: 2.3G (Tables: 1379) [--] Data in InnoDB tables: 2.7G (Tables: 284) [OK] Total fragmented tables: 0  -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA.  -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 620 basic passwords in the list.  -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION  -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3m 3s (36K q [199.732 qps], 1K conn, TX: 222M, RX: 33M) [--] Reads / Writes: 96% / 4% [--] Binary logging is disabled [--] Physical Memory     : 7.6G [--] Max MySQL memory    : 2.1G [--] Other process memory: 0B [--] Total buffers: 1.7G global + 3.2M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 1.7G (22.90% of installed RAM) [OK] Maximum possible memory usage: 2.1G (28.14% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (11/36K) [OK] Highest usage of available connections: 14% (22/151) [OK] Aborted connections: 0.00%  (0/1174) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) [!!] Joins performed without indexes: 50 [!!] Temporary tables created on disk: 70% (1K on disk / 2K total) [OK] Thread cache hit rate: 98% (22 created / 1K connections) [OK] Table cache hit rate: 98% (444 open / 450 opened) [!!] table_definition_cache(1200) is lower than number of tables(1928) [OK] Open file limit used: 5% (580/10K) [OK] Table locks acquired immediately: 99% (25K immediate / 25K locks)  -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed.  -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 8 thread(s). [--] Using default value is good enough for your version (10.3.20-MariaDB-log)  -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 35.6% (47M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/170.4M [OK] Read Key buffer hit rate: 99.9% (22M cached / 22K reads) [OK] Write Key buffer hit rate: 99.3% (824 cached / 818 writes)  -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 1.0G/2.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1). [--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.71% (10795186 hits/ 10826096 total) [OK] InnoDB Write log efficiency: 98.51% (39722 hits/ 40324 total) [OK] InnoDB log waits: 0.00% (0 waits / 602 writes)  -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 512.0M/1B [OK] Aria pagecache hit rate: 98.7% (130K cached / 1K reads)  -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.  -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.  -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.  -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server  -------- Recommendations --------------------------------------------------------------------------- General recommendations:     Control warning line(s) into /var/lib/mysql/errorlog.err file     Control error line(s) into /var/lib/mysql/errorlog.err file     MySQL was started within the last 24 hours - recommendations may be inaccurate     Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1     We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.              See https://dev.mysql.com/doc/internals/en/join-buffer-size.html              (specially the conclusions at the bottom of the page).     When making adjustments, make tmp_table_size/max_heap_table_size equal     Reduce your SELECT DISTINCT queries which have no LIMIT clause     Performance schema should be activated for better diagnostics Variables to adjust:     join_buffer_size (> 512.0K, or always use indexes with JOINs)     tmp_table_size (> 32M)     max_heap_table_size (> 32M)     table_definition_cache(1200) > 1928 or -1 (autosizing if supported)     performance_schema = ON enable PFS     innodb_buffer_pool_size (>= 2.7G) if possible.     innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.     innodb_buffer_pool_instances (=1) 

I’ve tried these suggestions along with every other possible thing it feels like (caching, optimizing tables etc) and still queries get stuck over and over, causing connections to be stuck in apache with a ‘sending reply’ status and causing websites not to load. What else can I try? Should I upgrade to a more powerful server?

Queries on knapsack

Given items with weights $ w_{1}, w_{2}, \dots, w_{n}$ and queries of form $ (l, r, w)$ asking for possibility to find a subset of items $ w_{l}, w_{l + 1}, \dots, w_{r}$ with total weight $ w$ , how to answer these queries with $ O(1)$ time by having done precalculation of some table in $ O(nW)$ time, where W is strict upper bound for $ w_{i}$ .

The time for precalculation looks similar to the straight 0-1 knapsack, but here I can’t see what we really should put in the table.

Dynamic media queries with CSS inside PHP file

I’m currently developing a WP plugin (specifically a set of Gutenberg blocks), in which I would like to have the ability to set a series of breakpoint values through the plugin options, to later be able to retrieve them and use them as CSS media queries values.

The only approach I’ve thought of so far is the one described in this article, which basically consists in switching style.css for style.php and do all the PHP stuff needed in there.

Is this an optimal way to solve this issue? Any other suggestions or clarifications will be more that welcome.

P.S. As I mentioned, it’s a set of Gutenberg blocks, so any React/JS approach would also be very welcome 😉

How to answer multiple queries for a tree?

I encountered an interesting problem based on tree-data-structure.

We are given a tree which has N nodes, with 1≤N≤105.

Time starts from second 1 and it continues for q seconds.

At each second, the value of each internal node is transferred to all of its child nodes. This happens with all the nodes, except leaf nodes.

Sometimes, at a given time p (seconds), we are asked to return the current value of node x.

There is this O(logN) approach: just find the pth ancestor of the given node x, and output its value.

A harder version of the same problem

Sometimes, at a given time p (seconds), we are asked to return the current value of node x, or we are said to update the value of node x to y.

How to solve this problem for q queries (seconds) efficiently, where 1≤q≤105.

Example

Input

N=5, q=8

Edges of the tree:-

4 3 3 1 5 2 1 2 

Values of nodes 1 to 5:-

1 10 4 9 4 

Queries:-

  • 1st second:- Add(1,6). Add the value 6 to node 1.
  • 2nd second:- What is the current value of node 3? (?,3)
  • 3rd second:- Add(3,5)
  • 4th second:- (?,3)
  • 5th second:- Add(2,2)
  • 6th second:- Add(5,10)
  • 7th second:- (?,5)
  • 8th second:- (?,4)

Expected Output

  • 6
  • 0
  • 33
  • 25

Explanation

  • 1st second: 6,1,1,13,14 (Values of all nodes)
  • 2nd second: 0,6,6,14,15
  • 3rd second: 0,0,5,20,21
  • 4th second: 0,0,0,25,21
  • 5th second: 0,2,0,25,21
  • 6th second: 0,0,0,25,33
  • 7th second: 0,0,0,25,33
  • 8th second: 0,0,0,25,33

Combine 3 queries for different lists into one

I am building API for client side apps. There are 3 API requests for 3 different lists as following:

tasksQuery = string.Format(@"<Where><And><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + FromDate + "</Value></Geq><And><Eq><FieldRef Name='TaskCompleted' /><Value Type='Boolean'>0</Value></Eq><Or><Eq><FieldRef Name='CreatedBy' /><Value Type='Text'>" + LoggedUser + "</Value></Eq><Or><Eq><FieldRef Name='Responsible' /><Value Type='Text'>" + LoggedUser + "</Value></Eq><Or><Eq><FieldRef Name='Accountable' /><Value Type='Text'>" + LoggedUser + "</Value></Eq><Or><Contains><FieldRef Name='Consulted' /><Value Type='Note'>" + LoggedUser + "</Value></Contains><Contains><FieldRef Name='Informed' /><Value Type='Note'>" + LoggedUser + "</Value></Contains></Or></Or></Or></Or></And></And></Where>")  meetingQuery = string.Format(@"<Where><And><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + FromDate + "</Value></Geq><Or><Eq><FieldRef Name='Organizer' /><Value Type='Text'>" + LoggedUser + "</Value></Eq><Eq><FieldRef Name='Creator' /><Value Type='Text'>" + LoggedUser + "</Value></Eq></Or></And></Where>")  newsQuery = string.Format(@"<Where><Eq><FieldRef Name='Status' /><Value Type='Text'>1</Value></Eq></Where><OrderBy><FieldRef Name='NewsArticleDate' Ascending='False' /></OrderBy>"; 

How can I combine all these queries in one query?