Hot answers tagged

6

Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?". Using parameterized queries ...


3

You are looking for a way to enter string constants. Say we want to enter “\X*$Y$!'Z,"” ; then, per the SQL standard, '\\X*$Y$!''Z,"' we \-escaped the \ we '-escaped the ' see http://brogoff.com/vertica/HTML/SQLRef/8953.htm this should work wherever SQL standard is supported. Oracle-specific: depends on column datatype; see https://...


2

In Postgres I would use: SELECT * FROM horarios AND (start_time, end_time) overlaps ($start_time, $end_time) AND "user" <> 'user_to_check'; This checks if there are no overlapping (start_time, end_time) intervals for a different user then the one for which you are trying to insert the new row. In fact, in Postgres you don't actually ...


2

For further context, the bash script is executed from a shell_exec in a php page (fastcgi/nginx). Try hard setting the path to the psql script so you don't rely on the environmental PATH and PHP setting that when it calls the shell, Rather than, $output = shell_exec('psql ...'); Try (or wherever command -v psql shows it's located) $output = shell_exec('/...


2

I believe you just need to insert a backslash behind every special character to escape it. The backslash is reserved as a special escape character in MySQL (and even escapes other backslashes) so that the proceeding character is treated as a literal. You can read more about it in the MySQL Docs. If you're using PHP, depending on what version, you can also ...


1

Full text searches have their good site, but need a bit of learning, but are interesting for big tables with alot of text. you can combine columns also for searches Also check the manual A small example how it works CREATE TABLe t1 (title varchar(20), FULLTEXT (title)) insert into t1 VALUES ("men\'s t'shirts"),("men\'s socks"),...


1

CREATE PROCEDURE has no IF NOT EXISTS. You have to drop it and create it a new Also php and mysqli don't add DELIMITER to the CREATe PROCEDURE, so you have to do it yourself. So the code must loook like DROP PROCEDURE IF EXISTS Insertion; DELIMITER $$ CREATE PROCEDURE Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN ...


1

A sketch for an alternative approach would be: -- supertype for user / group, it is not clear from your post what -- an appropriate name would be, so I'll just use `x`. each x is of a -- certain type. CREATE TABLE x_types ( x_type CHAR(1) NOT NULL PRIMARY KEY , -- maybe add a description ); INSERT INTO x_types (x_type) VALUES ('u'), ('g'); CREATE ...


1

As the comments mentioned, your question is lacking a lot of details. Specifically what is the criteria / logic you want to use to select 4 rows when idea with id = DC889 has 10 rows, for example? In any case, this will get you a random set of 4 rows for idea DC889: SELECT * -- Replace * with only the columns you need, as * is bad practice FROM IdeaTable ...


1

What would be easiest probably is to build a string list of the IDs that you need to filter on, in PHP first, and pass that in as a parameter to a procedure that uses that parameter with the FIND_IN_SET() function like so: SELECT * FROM SomeOtherTable WHERE FIND_IN_SET(IdField, IdListParameter)


1

Is there any way to collect somehow or write in another way query's which is repeated couple of times. I believe the term you're looking for is a "Data Abstraction Layer". This might take the form of some functions, or some classes with some methods on them, YMMV, but you basically build something that runs particular queries when they're called ...


1

Use instead: SELECT * FROM table WHERE label <> ALL ($1); Then you can pass an actual array for $1 - while (NOT) IN expects a list of values. See: How to use ANY instead of IN in a WHERE clause with Rails? If NULL values can be involved on either side, either query is problematic. You may want the form: SELECT * FROM table WHERE (label = ANY ($1) IS ...


1

I'm 99% sure that both PostgreSQL and all other SQL database softwares send this information back for each query somehow, but how? It doesn't. PHP's pg_query uses the simple query protocol and pg_query_params the extended query protocol, and in both cases what the client gets back for successful queries is essentially a CommandComplete message, where the ...


1

I'm not familiar enough with Laravel 7 to understand how well it implements alternate keys/composite keys, but this is basically what you've described: Notes: The alternate keys are crucial as an Id in and of itself does not provide uniqueness as required by the Relational Model. If I can spam "Customer 1, Dwayne Johnson" a million times into ...


1

In my case I ran into this where I didn't have access to edit any conf files on the server (.NET connecting to a managed db on DigitalOcean) so the other answers weren't an option. The host provided me a postgresql:// connection URL which had a ?sslmode= option on the end. I got the exact same error until I added "SSL Mode=Prefer;Trust Server ...


1

The problem you are going to face is called a race condition. Concurrency control is what controls concurrent access to a resource. The easiest way to avoid race conditions is to use mutex locks. This way only one person at a time can access it. Note that you cannot write your own lock systems since it still has a race condition. A mutex must be part of the ...


Only top voted, non community-wiki answers of a minimum length are eligible