put the result of a query into a variable and WITH statement

I need to put the result of a query into a variable.

Just a query, works as successful

DECLARE @count INT = (  SELECT count (*) FROM [AdventureWorks].[Person].[Address]     );  select @count; 

But if I need to use the WITH statement in a query, then I get a syntax error

DECLARE @count INT = (    WITH person_address (id)    as (     SELECT AddressID     FROM [AdventureWorks].[Person].[Address]    )    SELECT count (*)    FROM person_address     );  select @count; 

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘WITH’.

Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ‘)’.

How do I put the query value into a variable if the WITH clause is used in the SQL statement?

add_filter( ‘the_title’ gets through this if statement twice

I am using the accepted answer shown here: Apply the_title() filter in post & page title, but not in menu title

For some reason, the code is getting through this if statement twice:

if ( ! is_admin() && $  post instanceof WP_Post && ( $  post->post_type == 'page' ) && in_the_loop() && is_main_query() ) { 

I am already using the pre_wp_nav_menu and wp_nav_menu_items filters as shown on that answer.

What would be making this code run twice please? Or how would I debug to find out what is calling it and letting it through the if twice?

(When I use debug_backtrace() is shows apply_filters line 212 in plugins.php both times but I’m not sure what to do with that info)

Sorry, I would ask as a follow-up on the posted question, but I evidently can’t comment yet.

Do I need to create index for the column when using it in update statement?

I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table. I need to perform below update statement on the table.

update entity set name='xxxx' where userId = 'yyy.

The userId is in the where condition but I don’t know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

Case statement query

How can this be re-written. somehow not able to make it right. In case statement Then and ELSE are same would like to change it to single statement.

SELECT t.status_id status_id, ( CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return WHERE enabled = ‘D’ and firm_id=t.firm_id)

THEN (cl.category_code + ‘-‘ + s.status_name) ELSE cl.category_code + ‘-‘ + s.status_name END) AS status_type FROM bla bla

Split data across multiple rows in a single statement

I have data in table like

Customer Invoice No Date Value Paid Balance
ABC 1 01/12/2020 25 0 25
ABC 2 01/12/2020 50 0 50
XYZ 3 02/12/2020 200 0 200
XYZ 4 04/12/2020 100 0 100
ABC 5 04/12/2020 500 0 500

Now I received amounts for customers as below

ABC 540 XYZ 210

ABC XYZ
540 210

After receiving the amounts my table should like this

Customer Invoice No Date Value Paid Balance
ABC 1 01/12/2020 25 25 0
ABC 2 01/12/2020 50 50 0
XYZ 3 02/12/2020 200 200 0
XYZ 4 04/12/2020 100 10 90
ABC 5 04/12/2020 500 465 35

I got some clues but this works only for date based values but I need customer and date based.

Any help will be appreciated

How can I carry out SQL insert injection when there’s a select statement beforehand

So here’s the deal. I’ve been working on an SQL injection challenge and here’s what comes up.

There’s a registration page where you input your Username, password and confirm password. It’s vulnerable to INSERT SQL injection, I’m basically trying to insert my own data and make myself and admin (admin=1). However, there is a SELECT statement before the INSERT statement that checks if the username exists in the database. The problem is, if I try inserting data with SQL injection, the SELECT statement will fail and will generate an error, and the INSERT statement will never be executed.

I’ve made an in-a-nutshell PHP code to show you how it works.

<?php     $  username = $  _POST['username'];     $  password = md5($  _POST['password']);      $  sql = mysqli_query("SELECT * FROM users WHERE username = '$  username';");     if(mysqli_num_rows($  sql) > 0 || !$  sql) {         // this code will be run if the username already exists OR an SQL error in the query above.     }     else {         $  sql = mysqli_query("INSERT INTO users (`id`,`username`,`password`,`admin`) VALUES (NULL,'$  username','$  password',0);");     } ?> 

So the thing is, if I tried signing up with the username "admintest','password',1);-- " which should in theory INSERT myself into the database as an admin, here comes the problems.

The problem, is the SELECT query. Watch what happens.

SELECT * FROM users WHERE username = 'admintest','password',1);-- '; 

This of course is a syntax-error, and as we saw by the code I provided above, an IF statement will confirm that the SQL query was a syntax error, and the INSERT statement will NEVER run.

In an ideal world, this should happen in the INSERT statement, which will insert me as an ADMIN.

INSERT INTO users (`id`,`username`,`password`,`admin`) VALUES (NULL,'admintest','password',1);-- ','password',0); 

I’ve tried to work out something that doesn’t generate a syntax error on the SELECT, and also INSERTs the data I want to insert. Would be appreciated if anyone could help out 🙂

Understanding the proof of “DFS of undirected graph $G$, yields either tree edge or back edge” better with graph for each statement in proof

I was going through the edge classification section by $ \text{DFS}$ algorithm on an undirected graph from the text Introduction to Algorithms by Cormen et. al. where I came across the following proof. I was having a little difficulty in understanding the steps of the proof and hence I made an attempt to understand it fully by accompanying each statement in the proof with a possible graph of the situation.

Theorem 22.10 : In a depth-first search of an un-directed graph $ G$ , every edge of $ G$ is either a tree edge or a back edge.

Proof:

  1. Let $ (u , v)$ be an arbitrary edge of $ G$ , and suppose without loss of generality that $ d[u] < d[v]$ . Then, $ v$ must be discovered and finished before we finish $ u$ (while $ u$ is gray), since $ v$ is on $ u$ ‘s adjacency list.

  2. If the edge $ (u, v)$ is explored first in the direction from $ u$ to $ v$ , then $ v$ is undiscovered (white) until that time.

Figure 1

Figure 1 : Situation in point 2. DFS starts from ‘u’, ‘u’ is grayed and DFS then looks along the red arrow to ‘v’

  1. Otherwise we would have explored this edge already in the direction from $ v$ to $ u$ . Thus, $ (u, v)$ becomes a tree edge.

Figure 2

Figure 2 : Situation in point 3. DFS starts from ‘u’, ‘u’ is grayed, then discoveres ‘w’ and ‘w’ is grayed and then again discovers ‘v’ DFS then looks along the red arrow to ‘u’ , the green pointer explains the rest

  1. If $ (u, v)$ is explored first in the direction from $ v$ to $ u$ , then $ (u, v)$ is a back edge, since $ u$ is still gray at the time the edge is first explored.

Figure 3

Figure 3 : Situation in point 4. DFS starts from ‘u’, ‘u’ is grayed, then discoveres ‘w’ and ‘w’ is grayed and then again discovers ‘v’ DFS then looks along the red arrow to ‘u’ , ‘u’ is already grayed so the edge becomes a back edge, indicated by the green pointer


Could anyone confirm if I am on the right track or if not please rectify me.

[My question might seem similar to this or this but neither of them seemed to help me]

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server’s databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.

UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;

RANDWORD grabs a random word from a table containing approximately 100,000 English words.