MariaDB/MySQL database connexion error

[damien_mattei]

Hi,

i'm sticked on this database connexion error when i do:

(require db) ; for database connectivity
(mysql-connect #:user "mattei")
. . ../../../../Applications/Racket v8.14/collects/racket/private/kw.rkt:1313:23: mysql-connect: Access denied for user 'mattei'@'localhost'
  SQLSTATE: 28000

the strange thing is that i have no problem in command line to access the database :

mysql -u mattei                                 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 11.7.2-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;                                                                             +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oca_plasma         |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0,005 sec)

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0,003 sec)

i tried specifying other parameters without success too:

(mysql-connect #:user "mattei"
				     #:database "oca_plasma"
				     #:server "localhost"
				     ;;#:socket "c7668f958afc005e"
				     ;;#:socket "/tmp/mysql.sock"
				     )

[damien_mattei]

oh twas the classic SQL error that does not works in connexion ,even local if you do not specify a password, this solve it:

Change mysql user password using command line

(mysql-connect #:user "mattei" #:password "not-to-be-displayed-on-the-internet")
(object:connection% ...)

now got another error in the code but this another problem

query-rows: Operand should contain 1 column(s)
  SQLSTATE: 21000
  context...:
   /Applications/Racket/collects/db/private/generic/interfaces.rkt:170:0: raise-sql-error
   /Applications/Racket/share/pkgs/db-lib/db/private/mysql/connection.rkt:104:4: recv method in connection%
   /Applications/Racket/share/pkgs/db-lib/db/private/mysql/connection.rkt:551:8
   /Applications/Racket/collects/db/private/generic/common.rkt:308:13
   /Applications/Racket/collects/db/private/generic/common.rkt:215:18
   /Applications/Racket/collects/db/private/generic/common.rkt:180:8
   /Applications/Racket/collects/db/private/generic/common.rkt:209:4: call-with-lock* method in locking%
   /Applications/Racket/collects/db/private/generic/functions.rkt:90:0: compose-statement
   /Applications/Racket/share/pkgs/db-lib/db/private/generic/functions2.rkt:41:0: query-rows
   /Applications/Racket/collects/racket/contract/private/arrow-val-first.rkt:555:3
   /Users/mattei/Scheme-PLUS-for-Racket/if-parser.rkt:178:21
   body of "/opt/homebrew/var/www/drive/interpole_fields"

in :

#! /usr/bin/env racket
#lang reader SRFI-105
(require Scheme+)
...


(if db-flag

	  then ; use database

	     {row <- (query-rows dbc "select (value, unit) from constant where name = $1" keysw)}
	     {valor-unit <- row[1]}

	  else

...

[damien_mattei]

working code looks like that (using multiple columns in a single SQL query):

....

	(if db-flag

	  then ; use database

	     ;;{requete <- (string-append "select value, unit from constant where name = " (string #\") keysw (string #\"))}
	     {requete <- (string-append "select value, unit from constant where name = \"" keysw (string #\"))}
	     {rs-lines <- (query-rows dbc requete)} ; result lines
	     (display "interpole_fields : rs-lines= ") (display rs-lines) (newline)
	     {rows <- (first rs-lines)}
	     {valor-unit <- rows[1]}

	  else ; use files

           ........ )



	(if db-flag ; use database

	      {sizesw <- rows[0]}
	      
	   else ; use files

               ............. )

........


	    

[ryanc]

You should use parameterized queries instead of splicing the data into your query string. See SQL Injection in the docs, and read about SQL injection attacks in general.

[damien_mattei]

yes i know this form of attack,could be a risk as i use a web page (but not public now) to send the input data and test the code....

In fact i had used a parametrized query first but that did not worked, one reason was i use $1 but it is for PostgreSQL and i use MySQL/MariaDB which use ? for naming arguments, i corrected the code,the good parameterized working code is now this one:

{requete <- "select value, unit from constant where name = ?"}
{rs-lines <- (query-rows dbc requete keysw)} ; result lines

(supposing query-rows has the same protection than query-exec)

Thanks

[LiberalArtist]

@damien_mattei

In fact i had used a parametrized query first but that did not worked, one reason was i use $1 but it is for PostgreSQL and i use MySQL/MariaDB which use ? for naming argument

The excellent sql package by @ryanc lets you write SQL as S-expressions. It makes parameterized queries very easily, and it automatically emits the correct dialect of SQL for the database you are using.

[damien_mattei]

@LiberalArtist

The excellent sql package by @ryanc lets you write SQL as S-expressions.

I installed the library and yes it works :

(require sql) ; SQL: A Structured Notation for SQL Statements 
(select value unit #:from constant #:where (= name ,keysw))
(sql-statement "SELECT \"VALUE\", unit FROM constant WHERE (name = ?)" "rhoesw")

note value field of my constant table seems to be a reserved keyword (originating from RDF Core Data Model ?) but it is well handed by the library.

It even worked when putting (= name ,keysw) in infix : (name = ,keysw) with the condition of being in a curly infix expression of SRFI-105 for Racket between { } . Here is the trace of tests in REPL :

Welcome to DrRacket, version 8.14 [cs].
Language: reader SRFI-105, with debugging; memory limit: 8192 MB.
SRFI-105 Curly Infix parser for Racket Scheme and R6RS by Damien MATTEI
(based on code from David A. Wheeler and Alan Manuel K. Gloria.)

> (require db) ; for database connectivity


(require db)


#<eof>

> (define dbc (mysql-connect #:user "mattei" #:password "*****"))


(define dbc (mysql-connect #:user "mattei" #:password "*****"))


#<eof>

> (require sql)


(require sql)


#<eof>

> {requete <- (select value unit #:from constant #:where (name = ,keysw))}


($nfx$ requete <- (select value unit #:from constant #:where (name = ,keysw)))


#<eof>
> requete


requete
(sql-statement "SELECT \"VALUE\", unit FROM constant WHERE (name = ?)" "rhoesw")


#<eof>

> (query-rows dbc requete)


(query-rows dbc requete)
'(#(10.0 "cm⁻³"))


#<eof>
> 

because there is auto-detection of infix operators like = in Racket/Scheme+ even if they are nested in parenthesis (note: this is still features and code in development indeed.....)

and the program ,following the REPL tests, looks like this now:

(if db-flag

	  then ; use database

	     ;;{requete <- (string-append "select value, unit from constant where name = \"" keysw (string #\"))}
	     ;;{requete <- "select value, unit from constant where name = ?"}
	     ;;{rs-lines <- (query-rows dbc requete keysw)} ; result lines
	     ;;{requete <- (select value unit #:from constant #:where (= name ,keysw))} ; prefix
	     {requete <- (select value unit #:from constant #:where (name = ,keysw))} ; infix
	     {rs-lines <- (query-rows dbc requete)} ; result lines
	     (display "interpole_fields : rs-lines= ") (display rs-lines) (newline)
	     {rows <- (first rs-lines)}
	     {valor-unit <- rows[1]}

	  else ; use files
                     .......)