[
https://issues.apache.org/jira/browse/CASSANDRA-21139?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18055534#comment-18055534
]
Rishabh Saraswat commented on CASSANDRA-21139:
----------------------------------------------
!Positive Failure.png!
Steps to reproduce:
# In cassandra.yaml, change the fields use_misprepare_statements_enabled,
authenticator and authorizor to false, PasswordAuthenticator and
CassandraAuthorizer respectively.
# Start the server.
# In cqlsh, execute following
{code:java}
CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy',
'replication_factor': 1};
CREATE TABLE test.test (id int PRIMARY KEY, name text);
CREATE ROLE test_user WITH PASSWORD = 'pass' AND LOGIN = true;
GRANT SELECT ON KEYSPACE test TO test_user;{code}
# Execute the [^script.txt] in python environment.
Following logs found when following similar steps but
use_misprepare_statements_enabled set to true.
!Server Side Logging.png!
> Guardrail for miss-prepared statements
> --------------------------------------
>
> Key: CASSANDRA-21139
> URL: https://issues.apache.org/jira/browse/CASSANDRA-21139
> Project: Apache Cassandra
> Issue Type: Improvement
> Components: Feature/Guardrails
> Reporter: Brad Schoening
> Assignee: Rishabh Saraswat
> Priority: Normal
> Attachments: Positive Failure.png, Server Side Logging.png, script.txt
>
>
> We have hundreds of application teams and several dozen of them miss-prepare
> statements by using literals instead of bind markers.
> I.e.,
> {code:sql}
> // wrong
> session.prepare("select * from users where ID = 996");
> session.prepare("select * from users where ID = 997");
> session.prepare("select * from users where ID = 998");
> session.prepare("select * from users where ID = 999");
> // correct
> session.prepare("select * from users where ID = ?");
> {code}
> The problem causes the prepared statement cache to constantly overflow, and
> will print a prepared statements discarded WARN message in the Cassandra log.
> At present, we use a wack-a-mole approach to discuss the problem with each
> development team individually, and hope they fix it and train the entire team
> on how to prepare statements correctly.
> Also, finding the root cause of the issue today requires having the knowledge
> and access to look at the system.prepared_statements table.
> Guardrails would seem a good approach here, where the guard could WARN or
> REJECT when a statement was prepared using a WHERE clause and no bind markers.
> Note, this should not prevent users from creating prepared statements without
> a WHERE clause or with one or more literal values so long as there was at
> least one bind marker. Thus, the following would remain valid:
> {code:sql}
> session.prepare("select * from users");
> session.prepare("select * from users where TYPE=5 and ID = ?");
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
