My SQL query construction in python code is: When I run Bandit security tool, it says “Possible SQL injection vector through string-based query construction.” How do I avoid it? Answer Best practices recommend to avoid to dynamically build the query and instead use a parameterized query. But the goal is precisely to avoid what you are doing here: prevent a
Tag: sql
How to insert nested JSON array into SQL Server table
I am trying to insert a JSON file into a table using SQL Server’s “OPENJSON WITH (…” syntax). However, this file contains nested arrays, which I do not know how to handle. Here is my JSON file: } …
Why does the id serial primary key keep changing?
I have just started a full stack web developer course which includes PostgreSQL. I have been give some practice questions to do and when I clicked on run SQL it displays the id, first_name and last_name but when I entered in more lines of code to answer more questions and clicked on run SQL again, the id number changed to
Input parameters in procedure
Tell me how to solve such a case: There is a predicate in the stored procedure that searches for input_param by the input parameter: When calling this procedure, I need to pass two or much parameters: It naturally doesn’t work Are there any solutions? Answer Would this help? Sample data: Procedure:
How to format date that looks like this (20200326) MYSQL
I have two fields with dates in them. I am trying to format them to look like 03/26/2020 instead of 20200326. So far my code looks like this changed most of it due to my job guidelines so I can’t show you the exact code. Answer Use date_format():
Perform UNNEST, INNER JOIN and then ARRAY_AGG as part of an UPDATE query
I am trying to unnest an array from one table using ORDINALITY to preserve order, then perform an INNER JOIN on another table to find the corresponding value from a specific column and then use ARRAY_AGG to package this back up and UPDATE the original table. I have something working for a single query, but I want to do an
SQL date conversion to UNIX time stamp gives multiple UNIX times for same time stamp
When I attempt to convert UNIX timestamp to a timestamp recognized by SQL I get multiple UNIX timestamps. For example the image shows that the following code will provide two different date/times i.e 05/09/2019 00:00:00.00 and 19/03/2019 00:00:00.000 for the same UNIX time of 1567641600 Answer Ref: How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
Max analytical function plus windowed ordering is not working as expected
I have a table with status and location, the following is the data. I’d like to get max status partitioned by location using custom ordering. Any idea what needs to change? Right now it is giving only max value, irrespective of the ordering i mentioned. The custom ordering is 1 > 3 > 2 Expected result for location 11 :
How do I get Django to run a case-insensitive query against my MySql 5.7 db?
I’m using Django 2.0 and Python 3.7. I have this model and manager for attempting to find my model by a name, case-insentively. The backing database is MySql 5.7. Unfortunately, when the query actually gets created, it doesn’t appear Django is doing anything to account for case-insensitivity. This is an example query that results … What changes do I need
Append Row To Each Group in SQL
Let’s say that I have database table: Following: I get: However, for each group, I’d like to append an additional row with the value column taken from the id column so that the resulting table looks like: What is ANSI SQL (or most database agnostic) way to accomplish this? Answer You don’t want a group by in the initial set