user interface – Tool/media/platform to host query tool backed with Python

In making a simple query form, I am looking for a way to actualize this idea.

enter image description here

Basically,

  1. it let users to input some strings.
  2. by clicking the button, a Python script to examine if the user’s input matches any keys in a preset (Python) dictionary.
  3. if the input has a match, it returns the value. Otherwise an error message.

This tool will be used and circulated internally in a company.

Previously I want to make it in Excel using VBA to activate Python script. However online version of Excel cannot run VBA, and at the user end, there’s no Python installed.

I don’t know what tool and platform can help me to actualize this.

So I’m looking for other tool/media/platform, for example Microsoft Office family products can help me actualize this.

Can anybody please shed some light? Thank you.

301 redirect rewrite query string in .htaccess

RewriteRule ^/contact/?view=full$ /contact/ (R=301,L)

The RewriteRule pattern matches against the URL-path only, which notably excludes the query string. So, the above directive won’t match the required URL. To match the query string you need to use an additional condition and check against the QUERY_STRING server variable.

Also, in .htaccess (directory context) the URL-path matched by the RewriteRule pattern does not start with a slash, because the directory-prefix that is first removed from the URL that is matched by the pattern ends with a slash. So, again, the directive will not match the requested URL because of the slash prefix.

Also, the query string from the request is copied through to the substitution string by default – unless you explicitly discard or create a new query string. So, the above directive won’t remove the query string either.

Try the following instead, near the top of your .htaccess file:

RewriteCond %{QUERY_STRING} ^view=full$
RewriteRule ^(contact/)$ /$1 (QSD,R=302,L)

This matches the URL /contact/?view=full exactly. And redirects to /contact/. The QSD (Query String Discard) flag (Apache 2.4) on the RewriteRule directive discards the query string from the substitution string.

The $1 is a backreference to the captured group in the RewriteRule pattern, ie. the value contact/. This simply saves typing/repetition.

Note that this is currently a 302 (temporary) redirect. If this is intended to be permanent then only change it to a 301 once you have tested that it works OK. 301s are cached persistently by the browser, so can make testing problematic.

You should clear your browser cache before testing.

Reference:

query – How to reproduce SQL Injection problem by sending single quote in MySQL?

This is Damn Vulnerable Web Application (DVWA) and it’s vulnerable to SQL injection (SQLi).

Let’s begin by sending normal request

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit#

Output via browser

ID: 1
First name: admin
Surname: admin

This is how the request looks like in MySQL

mysql> SELECT first_name, last_name FROM users WHERE user_id = '1';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| admin      | admin     |
+------------+-----------+
1 row in set (0.00 sec)

mysql> 

Common way to identify SQL injection is by sending single quote ' char in the parameter.

E.g. id='

Give it a try on the url and it works.

http://127.0.0.1/dvwa/vulnerabilities/sqli/?id='&Submit=Submit#

Web browser will display SQL error indicates that the site is vulnerable to SQLi

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1

I didn’t know how the query looks like in MySQL ..

So I’ve tried SELECT first_name, last_name FROM users WHERE user_id = '''; but I didn’t get the same error.

Instead, I was getting '> symbol from MySQL shell.

mysql> SELECT first_name, last_name FROM users WHERE user_id = ''';
    '> 
    '> 
    '> '
    -> 
    -> ;
Empty set (0.00 sec)

mysql> 

What is the right way to query id=' or user_id = ' (single quote) request in MySQL?

group by in query google sheet select different columns

Want to get the results described on the screenshot. How can i achieve it. Thank you.

enter image description here

QUERY:

=QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")

Want to get First & last V5 value in terms of max and min dates in V2 (see K2, L2 desired results), I could get the last and first dates now i want their V5 corresponding.

java – SonarQube flagging String literal duplicated for Sql query bind parameters

We are using SonarQube lint for code quality analysis. The problem is, it is flagging query parameter binding literals as duplicated. However, declaring them as constant does not make sense.

Eg:

String queryStr = "select * from Person p where p.firstName=:firstName";
Query personQuery = session.createQuery(queryStr);
personQuery.setParameter("firstName", "Mark");

Sonarqube is flagging repeated String literal firstName. However, I feel the rule is invalid for this use case because declaring them as constant and changing them at one place won’t work. Because the parameter has to just match what is used in query String.

It does not involve any business logic or contract. Is my interpretation correct? If so, How do I go about convincing my peers about it?

query – Problem using the “listagg” function to produce the desired result in Oracle PL-SQL

I’m working with listagg and I have a problem here. It does not give the desired result and I just do not know where the problem is .

The deposit_query might be like this :

sum (dep_balance) >1 and
sum (turnover)    >1 and
DEP_BRANCH_COD     = -1 and
DEPOSIT_GROUP_DESC = -1 and 
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1

or it might be like this (without sum):

DEP_BRANCH_COD     = -1 and
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1 

The desired result for first condition is :

DEP_BRANCH_COD     = -1 and 
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1
group by customer_num
having sum (dep_balance) >1 and
       sum (turnover)    >1 and

and for second condition is :

DEP_BRANCH_COD     = -1 and 
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1

What I’ve written so far is the query below and the problem is that the final result has theGroup by customer_num having in the second condition.

declare

   deposit_query varchar(1000) := 'DEP_BRANCH_COD     = -1, 
                                   DEPOSIT_GROUP_DESC = -1, 
                                   DEPOSIT_TYPE_DESC  = -1, 
                                   CURRENCY_DESC      = -1';
   actual_query varchar2(1000);

begin

   select listagg(case
                    when stm not like '%sum(%' then stm end,
                      ' AND ') within group(order by stm) 
                       || 
          listagg(case
                    when stm like '%sum(%' then stm end, 
                      ' AND ') within group(order by stm) || ' Group by customer_num HAVING ' 
  into actual_query
  from (select regexp_substr(deposit_query, '(^and)+', 1, level) STM
        from dual
      connect by regexp_substr(deposit_query, '(^and)+', 1, level) is not null);

dbms_output.put_line(actual_query);

end;

I was wondering if you could tell me how to modify my query to have the desired result.

thanks in advance

templates – Can a link in WordPress contain a query string that is picked up as $_POST

This question is about a very specific circumstance.

  1. A template file can run a default category query with thumbnail, linked title and excerpt, or it can run a linked title only depending on a viewer choice. That is, once on the category page, the viewer can click a List Only option. https:// domain/category/ glossary-of-wood-terms ?format=list This part works fine.

  2. The second option (linked title only) should pick up the whole dataset, not a paginated dataset.

  3. Using a pre_get_posts, I can set the query to get the whole dataset (not paginated) with

$query->set( 'posts_per_page', -1 );

and an if test that will only run that code on a particular URL. Here is where the problem lies.

It turns out that WordPress sees the address as https://domain/category/glossary-of-wood-terms?category_name=glossary-of-wood-terms with the query string hidden. But WordPress is treating it as a $_POST variable not a $_GET variable.

What address should I used in the pre_get_posts test?

domain driven design – CQRS denormalized data query with multiple aggregates

Let’s say there’s a domain that’s similar to Reddit.

Aggregate roots are

Board // you can ignore this
  - boardId
Post
  - postId
  - userId
User
  - userId
  - username

where each aggregate emits

Post
  - PostCreated
User
  - UserCreated
  - UsernameUpdated

for the query side, the query will try to fetch a page of posts in a specific board.

Given this, the denormalized data should look as below

posts: ({
  title: 'some post title',
  body: 'some post body',
  author: 'user123',
})

Now in the denormalised database, I’d create post entry when the PostCreated event is received. And the received event holds userId.

To populate the author field for the read model above with that user ID, I can do either

  1. Read username from the existing denormalized User data, and save the post read model with the username.
    • This will require updating ALL posts when UsernameUpdated event is handled.
  2. Create a join column between User and Post. and when query is requested, join the table to populate the author field with username

Is it so obvious that the second method is the way to do it? The reason why it’s confusing me is that the denormalised database feels almost like a giant monolithic database (thinking of adding other aggregate root’s events to the read model E.g., Board). Or is denormalized data is just like this?

sql server – query to find incorrect data

A table contains valid data and invalid data. Require query to find invalid data.
Valid Data : A locatorcode can contain multiple countycode and when even one record with serviceenddate = 12/31/9999 for a locator code.
Eg:enter image description here

InvalidData : when service end is not having 12/31/9999
enter image description here

Please help in query for finding invalid records in same table with more than million records.

stored procedures – MySQL – Pass input parameter into Cursor query

Is it possible to pass input parameter into Cursor

SELECT statement WHERE clause?

For some reason I think it isn’t working.

I’m trying to pass _TAG and _ITEM_NAME into where clause.

    DECLARE cursor_test cursor 
for SELECT itemid  
FROM items 
WHERE  key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" 
AND STATUS = '0';

Here is the the Stored procedure:

    DELIMITER //
    CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20), 
                                  _TAG VARCHAR(20), 
                                  _ITEM_NAME VARCHAR(50))
    BEGIN
    declare FINISHED BOOL default false;

    DECLARE cursor_test cursor for SELECT itemid  
FROM items 
WHERE hostid = @_HOSTID AND key_ 
LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" 
AND STATUS = '0';

    DECLARE CONTINUE HANDLER for not found set FINISHED := true;

    SET @HOSTNAME = _HOSTNAME;

    PREPARE STMT1 FROM

    "SELECT hostid INTO @_HOSTID FROM hosts  WHERE NAME = ?";

    EXECUTE STMT1 USING @HOSTNAME;
    DEALLOCATE PREPARE STMT1;

    open cursor_test;
       SET @TOTAL_VALUE := 0;
       loop_itemid: loop
            fetch cursor_test into _ITEMID;
            SELECT _ITEMID;
            if FINISHED then
                leave loop_itemid;
            end if;
            SET @TOTAL_VALUE := @TOTAL_VALUE + 
             (SELECT value from history_uint WHERE itemid = _ITEMID 
               ORDER BY clock DESC LIMIT 1);

       end loop loop_itemid;
       SELECT @TOTAL_VALUE;
    close cursor_test;

    END //