Python错误1064:SQL语法中有错误| Arduino Serial

Question

每次运行它都会给我这个错误

Exception has occurred: ProgrammingError
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' CURRENT_DATE(), CURRENT_TIME())' at line 2

查询通过USB从arduino获取数据。几天前，它通过一个键盘完美地工作，以测试它是否工作，直到我尝试用RFID阅读器获取ID。我只将列ID更改为SN以匹配数据库。如果我将ID直接放入查询，它会神奇地工作。我不知道这是否与我的arduino上的代码有关

Python代码：

while True:
    data = arduino.readline().decode('ascii')
    if data != '': 
        print(data)
        
        cursor.execute("INSERT INTO `Anwesenheit` (SN, Datum, Uhrzeit) VALUES ({}, CURRENT_DATE(), CURRENT_TIME());".format(data))
        db.commit()

以前的Arduino代码：

#include <Keypad.h>

const byte ROWS = 4; 
const byte COLS = 4; 

char hexaKeys[ROWS][COLS] = {
  {'1', '2', '3', 'A'},
  {'4', '5', '6', 'B'},
  {'7', '8', '9', 'C'},
  {'*', '0', '#', 'D'}
};

String nummer;


byte rowPins[ROWS] = {9, 8, 7, 6}; 
byte colPins[COLS] = {5, 4, 3, 2}; 

Keypad customKeypad = Keypad(makeKeymap(hexaKeys), rowPins, colPins, ROWS, COLS); 

void setup(){
  Serial.begin(9600);

}
void loop(){
  
  char key = customKeypad.getKey();

  if (key){
   // Serial.println(key);
    if(key == '*') {
      nummer = ""; 
    } else if(key == '#') {
      
        Serial.println(nummer);
        nummer = "";
        
    } else {
      nummer += key;
    }
  }
}

Arduino代码现在：

#include <Wiegand.h>
#define PIN_D1 3

Wiegand wiegand;

void setup() {
  Serial.begin(9600);

  wiegand.onReceive(receivedData, "");
  wiegand.onReceiveError(receivedDataError, "Card read error: ");
  wiegand.onStateChange(stateChanged, "");
  wiegand.begin(Wiegand::LENGTH_ANY, true);

  pinMode(PIN_D0, INPUT);
  pinMode(PIN_D1, INPUT);
  attachInterrupt(digitalPinToInterrupt(PIN_D0), pinStateChanged, CHANGE);
  attachInterrupt(digitalPinToInterrupt(PIN_D1), pinStateChanged, CHANGE);
  
  pinStateChanged();
}
void loop() {
  noInterrupts();
  wiegand.flush();
  interrupts();
  delay(100);
}

// When any of the pins have changed, update the state of the wiegand library
void pinStateChanged() {
  wiegand.setPin0State(digitalRead(PIN_D0));
  wiegand.setPin1State(digitalRead(PIN_D1));
}

// Notifies when a reader has been connected or disconnected.
// Instead of a message, the seconds parameter can be anything you want -- Whatever you specify on `wiegand.onStateChange()`
void stateChanged(bool plugged, const char* message) {
    Serial.print(message);
    Serial.println(plugged ? "" : "DISCONNECTED");
}

// Notifies when a card was read.
// Instead of a message, the seconds parameter can be anything you want -- Whatever you specify on `wiegand.onReceive()`
void receivedData(uint8_t* data, uint8_t bits, const char* message) {
    
    //Serial.print(bits);
    //Serial.print("bits / ");
    //Print value in HEX
    uint8_t bytes = (bits+7)/8;
    for (int i=0; i<bytes; i++) {
        Serial.print(data[i] >> 4, 16);
        Serial.print(data[i] & 0xF, 16);
    }
    Serial.println();
}

// Notifies when an invalid transmission is detected
void receivedDataError(Wiegand::DataError error, uint8_t* rawData, uint8_t rawBits, const char* message) {
    Serial.print(message);
    Serial.print(Wiegand::DataErrorStr(error));
    Serial.print(" - Raw data: ");
    Serial.print(rawBits);
    Serial.print("bits / ");

    //Print value in HEX
    uint8_t bytes = (rawBits+7)/8;
    for (int i=0; i<bytes; i++) {
        Serial.print(rawData[i] >> 4, 16);
        Serial.print(rawData[i] & 0xF, 16);
    }
    Serial.println();
}

Answer 1

data值被格式化为不带引号的字符串，打破了SQL语法。使用绑定变量可以解决此问题（如果数据不可信，还可以保护代码免受SQL注入攻击）：

cursor.execute("INSERT INTO `Anwesenheit` (SN, Datum, Uhrzeit) VALUES (%s, CURRENT_DATE(), CURRENT_TIME(), (data,))