Welcome!

Welcome to the official BlackBerry Support Community Forums.

This is your resource to discuss support topics with your peers, and learn from each other.

inside custom component

Native Development

Reply
Developer
SumiSadiq
Posts: 148
Registered: ‎04-08-2013
My Device: Blackberry 10
My Carrier: Blackberry
Accepted Solution

insert data to DB avoiding SQL Injection

Hi,

 I have used the following method to insert data to DB

 

1)I had a 'DB-Helper class' and in that I have the following function

void dbHelper::createOrUpdateRecord(const QString Insertquery) {
	QSqlDatabase database = QSqlDatabase::database();
	QSqlQuery query(database);
	query.prepare(Insertquery);

	if (query.exec()) {
	   alert(tr("Record created"));
	} else {
		const QSqlError error = query.lastError();
		alert(tr("Create record error: %1").arg(error.text()));
	}
	database.close();
}

 2)Where ever I want to insert Data to DB I created the insert Query as a string and Pass the string as parameter to the above function

 

createOrUpdateQuery =("INSERT INTO tutorial (title,titleArabic,shortDesc,shortDescArabic,description,descriptionArabic,externalLink,tutorialId,isActive) VALUES(\""
+ map.value("title").toString() + "\", \""	
+ map.value("titleArabic").toString() + "\",\""	
+ map.value("shortDesc").toString() + "\",\""					
+ map.value("shortDescArabic").toString() + "\",\""					
+ map.value("description").toString() + "\",\""					
+ map.value("descriptionArabic").toString() + "\",\""					
+ map.value("externalLink").toString() + "\",\""					
+ map.value("tutorialId").toString() + "\",\""					
+ map.value("isActive").toString() + "\" )");
		}


dbHelp.createOrUpdateRecord(createOrUpdateQuery);

 I read that This method will cause Sql Injection.And also using this method we can insert only string values as data.

 

My question is :

 

I read that the best method to insert data is using 'bind'.So if Iam trying to use the 'bind' method then I will not be able to make the insert to db as a generic function.Is that possible.Plz help me out to make the insert data to db as a generic fn 

Please use plain text.
Developer
YamilBracho
Posts: 713
Registered: ‎05-31-2010
My Device: 8520
My Carrier: Movistar

Re: insert data to DB avoiding SQL Injection

I didn't test this but it could give you an idea :

sql = "INSERT INTO tutorial (title,titleArabic,shortDesc,shortDescArabic,description,descriptionArabic,externalLink,tutorialId,isActive) "
+ "VALUES(:title, :titleArabic, :shortDesc, :shortDescArabic, :description, :descriptionArabic, :externalLink, :tutorialId, :isActive)"

createOrUpdateRecord(sql, map);

void dbHelper::createOrUpdateRecord(const QString Insertquery, QMap<QString, Qvariant> paramMap {
	QSqlDatabase database = QSqlDatabase::database();
	QSqlQuery query(database);
	query.prepare(Insertquery);
	
	QMap<QString, QVariant::const_iterator it = paramMap.constBegin();
	while (i != map.constEnd()) {
		query.bindValue(it.key(), it.value());
		qDebug() << it.key() << ": " << it.value();
		++it;
	}	
	
	if (query.exec()) {
	   alert(tr("Record created"));
	} else {
		const QSqlError error = query.lastError();
		alert(tr("Create record error: %1").arg(error.text()));
	}
	database.close();
}

 

 

Please use plain text.
Developer
peardox
Posts: 1,229
Registered: ‎03-20-2011
My Device: Playbook, Z10 LE, Dev Alpha B, 2x Dev Alpha C
My Carrier: 3, Orange, Vodafone

Re: insert data to DB avoiding SQL Injection

Generally you escape any language in you SQL query

 

All modern languages support this concept

 

e.g. (using MySQL syntax)

 

select fred from abc; insert into users ('user', 'pass') values ('fred', 'pass')

 

The ciritical part is the semi-colon in the middle

 

This is your basic injection method

 

For example - you have an unprotected site that passed strings using get - so mine ends us as fred + pass

 

I can simply add the SQL to the end of pass and create a new user

 

Escaping strings is VITAL for safety as this now places a \ in front of every dangerous character (SQL falls over - the query never gets executed)




Click the like button if you find my posts useful!
Please use plain text.
Developer
SumiSadiq
Posts: 148
Registered: ‎04-08-2013
My Device: Blackberry 10
My Carrier: Blackberry

Re: insert data to DB avoiding SQL Injection

Thanks friend,

   I modified your answer a bit and I reached to the solution

  

sql = "INSERT INTO tutorial (title,titleArabic,shortDesc,shortDescArabic,description,descriptionArabic,externalLink,tutorialId,isActive) VALUES(:title, :titleArabic, :shortDesc, :shortDescArabic, :description, :descriptionArabic, :externalLink, :tutorialId, :isActive)"

createOrUpdateRecord(sql, map);

void dbHelper::createOrUpdateRecord(const QString Insertquery, QVariantMap paramMap {
	QSqlDatabase database = QSqlDatabase::database();
	QSqlQuery query(database);
	query.prepare(Insertquery);
	
for (QVariantMap::iterator it = paramMap.begin();it != paramMap.end(); it++)
		{
			//query.bindValue(it.key(), it.value());
			query.bindValue(":"+it.key(), it.value());
			qDebug() << it.key() << ": " << it.value();
		}	
	
	if (query.exec()) {
	   alert(tr("Record created"));
	} else {
		const QSqlError error = query.lastError();
		alert(tr("Create record error: %1").arg(error.text()));
	}
	database.close();
}

 Please varify this.Thanks a lot for your time

Please use plain text.