Improving: Insert up to X verified links from project

The “Insert up to X verified links from project” function is excellent, however, when i choose 10 for example, all 10 url anchors are the same! For example, all 10 URLs created in the article are different, but all have the same anchor “plumber Detroit”. This looks quite suspicious, and can be quite a fo0tprint. 

So, if possible, can we have randomised keywords from the Keywords section? So if I have 10 URLs in a single article created, then maybe it uses 10 different keywords. This way, it looks more natural.

Ensuring relationship between child tables exists prior to SQL insert

I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.

I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.

The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I’m wondering if I can accomplish both in one query though. I’m not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.

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 🙂

How to insert text in Table

I have

Table[{i^2, "mod",  i + 1, "=", Mod[i^2, i - 1]}, {i, 3, 4}] 

which gives me

{{9, mod, 4, =, 1}, {16, mod, 5, =, 1}} 

But I want to get

{{9 mod 4 = 1}, {16 mod 5 = 1}} 

How can I get rid of the commas in the output? I can drop the first comma in Table after i^2 but when dropping other commas things get messy. What am I doing wrong? What are the rules? I can’t find anything in Help.

wp insert post not working

my wp insert post not working. In post_title and post_name he not save the variable. So at place of Service + variable , i get only "Service", why?

function mic_create_new_page() {     global $  user_ID;         $  new_post = array(             'post_title' => 'Service ' . $  secteur,             'post_content' => '[makeitseo-keyword]',             'post_status' => 'publish',             'post_date' => date('Y-m-d H:i:s'),             'post_author' => $  user_ID,             'post_type' => 'page',             'post_name' => $  slugmic         );     global $  wpdb;     $  tableau_post = array ();     $  tableau_post = mic_stock_in_array($  tableau_post); $  res = $  wpdb->get_results('select * from wp_secteurs');   foreach ( $  res as $  ville ) {     $  id = $  ville->id_secteurs;     $  secteur = $  ville->libelle;     $  slugmic = strtolower(str_replace(" ","-",$  secteur))."-s". $  id ;      if(!in_array(normalize($  slugmic), $  tableau_post))         $  post_id = wp_insert_post($  new_post);              } 

}

SQLite constraint violation – How to insert data to this schema?

First of all, sorry for the question but I was hours searching for a solution and the only things that I got are Java exceptions. I’m developing a little web crawler for movies, so I created this two tables.

CREATE TABLE IF NOT EXISTS movies(     movie_id INTEGER PRIMARY KEY,     title VARCHAR(100) NOT NULL UNIQUE,     info VARCHAR(250) );  CREATE TABLE IF NOT EXISTS genres(     genre_id INTEGER PRIMARY KEY,     title VARCHAR(50) NOT NULL UNIQUE, ); 

Well, as you can see is a many to many relationship, so I made a junction table like this:

CREATE TABLE IF NOT EXISTS movies_genres(     movie_id INTEGER NOT NULL,     genre_id INTEGER NOT NULL,     PRIMARY KEY(movie_id, genre_id),     FOREIGN KEY (movie_id) REFERENCES movies(movie_id),     FOREIGN KEY (genre_id) REFERENCES genres(genre_id) ); 

And I’m trying to insert data with (arg are the method arguments):

INSERT INTO movies_genres(movie_id, genre_id)  SELECT m.movie_id, g.genre_id FROM SELECT (movie_id FROM movies WHERE title = arg) AS m CROSS JOIN SELECT (genre_id FROM genres WHERE title = arg) AS g; 

But I get this SQLITE_CONSTRAINT] Abort due to constraint violation (UNIQUE constraint failed. I don’t know where is the error, maybe is in the schema but I can’t see the error. Any idea on how fix it?.

How can I check if BULK INSERT is running on a table?

A BULK INSERT command may take a long time to run.

I have a program that receives multiple files and pushes them to the DB using the BULK INSERT command to a specific table. After a file is uploaded, the program deletes it and moves to the next.

The BULK INSERT command may continue to run on the DB after the program stopped. so a file may be loaded to the DB, or still in process when my program starts.

So, my questions are:

  1. Can I tell if a BULK INSERT command is still running for a specific table?
  2. Can I tell if a file has been already loaded to the DB?

Generate all combinations for variables and insert into temp table

0

I have one requirement there are 4 variables and i want all combinations of 4 variables and insert into temp table .

DECLARE StartDateTime DATETIME; DECLARE Age INT;  DECLARE Duration INT ; DECLARE TotalDD INT;  CREATE TEMPORARY TABLE tempTable(     Duration INT,     TotalDD INT,     Age INT,     StartDateTime DATETIME,     ); SET Age = 16; SET TotalDD = 14; SET Duration = 30; SET StartDateTime = CURDATE(); 

Excepted Result:

Duration age TotalDD StartDateTime 30 null null null null 16 null null 30 null null null 30 16 null null null null 14 20200622 30 null 14 null 30 16 null 20200622 

……….. ………. so on

postgresql: filter with subquery and then insert

having the following query, my plan was to filter with the subquery first and then to INSERT:

INSERT INTO cals_new (listing_id,date,available,price,timestamp)                                          (SELECT listing_id,date,available,price,timestamp                                           FROM   cals c                                           WHERE  NOT EXISTS ( SELECT                                                                FROM   update                                                                WHERE  id = c.id                                                                )); 

But EXPLAIN gives the following:

QUERY PLAN

Insert on cals_new (cost=513077.42..5338467.65 rows=109800833 width=44) -> Merge Anti Join (cost=513077.42..5338467.65 rows=109800833 width=44) Merge Cond: (c.id = update.id) -> Index Scan using cals_pkey on cals c (cost=0.57..3958576.01 rows=113119496 width=44) -> Sort (cost=513076.85..521373.51 rows=3318663 width=8) Sort Key: update.id -> Seq Scan on update (cost=0.00..62881.63 rows=3318663 width=8) 

If I understend the EXPLAIN output correctly, it does the expansive INSERT first, and only filters after.

How to optimise the query so that it works as expected? More specifically, filters with the subquery first and then does the INSERT?

Thank you!

Insert a row into the table omitting all values?

Suppose I have a table with a serial as primary key and couple of other columns, that are all nullable. Is there any way to insert a new row in that table by omitting all the columns in the insert clause? Since serial is auto-incremental and can be omitted right? The rest of columns can be null and can therefore be omitted too? So theoretically it should be possible somehow. But is it?