Query Builder
package query_builder | |
import ( | |
"strings" | |
"strconv" | |
"html/template" | |
"fmt" | |
) | |
type DynamicQueryBuilder string | |
type QueryParams map[string]string | |
func (qp QueryParams) GetInt(key string) interface{} { | |
mapVal := qp[key] | |
if mapVal == "" { | |
return "" | |
} else { | |
val, err := strconv.Atoi(mapVal) | |
if err != nil { | |
return "" | |
} | |
return val | |
} | |
} | |
func (qp QueryParams) GetString(key string) interface{} { | |
return qp[key] | |
} | |
type Expression struct { | |
Key string | |
Exp string | |
Value interface{} | |
} | |
func (dqb DynamicQueryBuilder) NewExp(key string, assignment string, value interface{}) Expression { | |
return Expression{Key: key, Exp: assignment, Value: value} | |
} | |
func componentToString(c interface{}) DynamicQueryBuilder { | |
switch v := c.(type) { | |
case Expression: | |
return DynamicQueryBuilder(c.(Expression).ToString()) | |
case string, *string: | |
return DynamicQueryBuilder(c.(string)) | |
case DynamicQueryBuilder: | |
return v | |
default: | |
return "" | |
} | |
} | |
func (dqb DynamicQueryBuilder) And(component ...interface{}) DynamicQueryBuilder { | |
return dqb.getOperationExpression("AND", component...) | |
} | |
func (dqb DynamicQueryBuilder) OR(component ...interface{}) DynamicQueryBuilder { | |
return dqb.getOperationExpression("OR", component...) | |
} | |
func (dqb DynamicQueryBuilder) getOperationExpression(operation string, component ...interface{}) DynamicQueryBuilder { | |
if len(component) == 0 { | |
return "" | |
} | |
if len(component) == 1 { | |
return componentToString(component[0]) | |
} else { | |
clauses := make([]string, 0) | |
for _, v := range component { | |
value := componentToString(v) | |
if value != "" { | |
clauses = append(clauses, ""+string(value)+"") | |
} | |
} | |
if len(clauses) > 0 { | |
return DynamicQueryBuilder("( " + strings.Join(clauses, " "+operation+" ") + ")") | |
} | |
return "" | |
} | |
} | |
func (dqb DynamicQueryBuilder) Limit(offset int, length int) DynamicQueryBuilder { | |
query := string(dqb) | |
query += " LIMIT " + strconv.Itoa(length) + " OFFSET " + strconv.Itoa(offset) | |
return DynamicQueryBuilder(query) | |
} | |
func (dqb DynamicQueryBuilder) CopyQuery(dest *string) DynamicQueryBuilder { | |
*dest = dqb.ToString() | |
return dqb | |
} | |
func (dqb DynamicQueryBuilder) BindSql(sql string) string { | |
if dqb != "" && dqb != "( )" { | |
index := strings.Index(dqb.ToString(), "LIMIT") | |
if index == 1 { | |
return sql + dqb.ToString() | |
} | |
return sql + " WHERE " + string(dqb) | |
} | |
return sql | |
} | |
func (dqb DynamicQueryBuilder) ToString() string { | |
return string(dqb) | |
} | |
func (e Expression) ToString() string { | |
switch e.Value.(type) { | |
case int, int16, int32, int64: | |
val := strconv.Itoa(e.Value.(int)) | |
clause := e.Key + e.Exp + e.getReplaceExp() | |
return fmt.Sprintf(clause, val) | |
default: | |
if strings.TrimSpace(e.Value.(string)) == "" { | |
return "" | |
} else { | |
e.Value = template.HTMLEscapeString(e.Value.(string)) | |
clause := e.Key + e.Exp + e.getReplaceExp() | |
val := fmt.Sprintf(clause, e.Value) | |
return val | |
} | |
} | |
return "" | |
} | |
func (e Expression) getReplaceExp() string { | |
switch e.Value.(type) { | |
case int, int64, int32, int16: | |
return "%s" | |
default: | |
return "'%s'" | |
} | |
} |
This comment has been minimized.
This comment has been minimized.
I m using HTMLEscapeString to make it safe for SQL injection. |
This comment has been minimized.
This comment has been minimized.
fakihariefnoto
commented
Oct 19, 2018
This is good but you still need to sanitize input from user to prevent fro. sqli. HTML Escape does works for preventing XSS. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
pieterclaerhout commentedSep 18, 2018
Seems like the wrong way to do it. You should really think about using placeholders in your queries instead of inserting the values. That would at least make it safe for SQL injection…