I'm trying to retrieve some data from a mysql database with the code:
app.get("/api/getStudentsFromClass", async(req,res) => {
const currentClassClicked = req.query.currentClassClicked
connection.query(
"SELECT * FROM ?",
[currentClassClicked],
(err,result) => {
if(result){
console.log(result)
}
if(err){
console.log(err)
}
}
)
})
The front end:
const currentClassClicked = localStorage.getItem("currentClassClicked")
const [students, setStudents] = useState("")
Axios.get("http://localhost:1337/api/getStudentsFromClass", {
params: {currentClassClicked}
}).then((response) => {
setStudents(response.data.message)
console.log(students)
})
However it says there's an error in mysql statement and shows that the statement is this
sql: "SELECT * FROM '13a1'"
I don't know why it's '13a1' and not 13a1. Thank you
Because you're telling that 13a1 is a string (as opposed to a table name):
"SELECT * FROM ?",
[currentClassClicked],
The whole point of prepared statements and bound parameters is to separate code from data.
The only way to secure your current design is to have a hard-coded list of tables, verify that received input maps a known table and build SQL dynamically, as in "SELECT * FROM " + currentClassClicked. But the overall idea looks strange. One would expect to find a master table with all classes and a child table with the information identified by class ID.
As per the follow-up comments, you don't create a new table for each student—there's no reason to handle classes different. However, if you want to keep your current design you'll have to hard-code tables in the application code or assume that it isn't secure and PII data could eventually be exposed.